MySQL issue: LEFT JOIN on empty table

Question

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 1

Since you didn't post your actual SQL, i just make assumption here. My experience telling me that you might have a where clause that causes the SQL to return empty set.

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

The above SQL will return empty result set. You may need to modify your SQL into the following format, by bring up the problematic where clause to LEFT JOIN ON clause.

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

Answer 2

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.