mysql 左表为null_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).

Pseudo code:

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

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.

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

解决方案

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;

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;

)

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.

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