MySQL LEFT JOIN-当最左边的表为空时如何仍然返回结果?

问题描述：

我正在尝试将三个单独的查询合并为一个.每个查询从不同的表中选择一个不同的列，但是这些表确实有一些共同的列.

I am trying to combine three separate queries into one. Each query selects a different column from a different table, but the tables do have some columns in common.

我尝试做的是将两个表左联接到另一个表，只要最左边的表返回结果，此方法就很好用.但是，如果最左边的表没有返回结果，那么根本不会返回任何结果(即使两个被左联接的查询都有结果).

What I tried doing was LEFT JOINing two of the tables to the other, and this works great as long as the leftmost table returns a result. But if the leftmost table does not return a result, then no result is returned at all (even if the two queries being LEFT JOINed have results).

伪代码:

SELECT a.col_1, b.col_2, c.col_3
FROM tableA a 
LEFT JOIN tableB b ON b.someCol = a.someCol
LEFT JOIN tableC c ON c.someCol = a.someCOL
WHERE a.anotherCol = :someVal AND a.yetAnotherCol = :anotherVal

如果tableA有结果，那么查询结果就是我想要的(即它返回三列，分别为col_1，col_2 | null和col_3 | null).但是，如果tableA没有结果，则查询返回null.

If tableA has a result, then the query result is what I'm wanting (i.e. it returns three columns with the col_1, col_2|null and col_3|null). But if tableA doesn't have a result, then the query returns null.

当LEFT JOIN字符串中最左边的表没有结果时，还有什么方法可以返回结果吗?

Is there any way to still return results when the leftmost table in a string of LEFT JOINs doesn't have a result?

答

您遇到了问题. left join将所有内容保留在第一个表中.听起来您想要的更像是full outer join，但MySQL不支持.但是，有一个不错的解决方法.

You have a problem. The left join keeps everything in the first table. It sounds like you want something more like a full outer join, which MySQL does not support. But, there is a nice workaround.

它听起来像您的查询具有以下形式:

It sonds like your query is of the form:

select . . .
from t1 left join
     t2
     on t1.t1id = t2.t1id left join
     t3
     on t1.t1id = t3.t1id;

将所有ID组合在一起，然后进行联接:

Bring all the ids together and then do the joins:

select . . .
from (select t1id
      from ((select t1id from t1) union
            (select t1id from t2) union
            (select t1id from t3)
           ) driver left join
           t1
           on t1.t1id = driver.t1id left join
           t2
           on t2.t1id = driver.t1id left join
           t3
           on t3.t1id = driver.t1id;
     )

driver将包含您要从其他表中获取的所有"t1" ID.它可能不包括所有表，因为有些表可能会连接其他ID.

The driver would consist of all the "t1" ids that you want from the other tables. It might not include all the tables, because some might be joining on other ids.

而且，driver子查询中的union是有意的.您要删除重复项.

And, the union in the driver subquery is intentional. You want to remove the duplicates.

MySQL LEFT JOIN-当最左边的表为空时如何仍然返回结果?

相关推荐