ashays ashays - 4 months ago 11
SQL Question

MySQL issue: LEFT JOIN on empty table

Given a database with two tables

X
and
Y
, I have a query that should
LEFT JOIN
the two tables on attributes
X.a1
and
Y.b1
. I used the following query:

SELECT X.a1, X.a2, Y.b1, Y.b2 FROM X LEFT JOIN Y ON (X.a1 = Y.b1)


I thought that would be good enough to work, even if
Y
is currently an empty table. However, the query breaks because table
Y
is empty, it seems. Is there any way to reformat this query so that even if
Y
is an empty table, the
LEFT JOIN
will not break? Or do I just need to always make sure that there is some data in table
Y
, even if it doesn't match anything in table
X
(hence the
LEFT JOIN
).

Answer

Your table names are a little confusing. Is it X and Y, or X.a and Y.b?

If X and Y:

SELECT X.a1, X.a2, Y.a1, Y.b2 FROM X LEFT OUTER JOIN Y ON (X.a1 = Y.b1)

should bring back all X, with nulls for the Y.a1 and Y.b2 where there is no matching record.

Comments