CODEWITHSUNDEEP
mysqlleft-join
ashays
ashays

Reputation: 1154

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).

Upvotes: 7

Views: 16548

Answers (3)

TomoMiha
TomoMiha

Reputation: 1279

Try your query on some Sql editor that returns errors like HeidiSQL or similar. In my case the problem was ambiguous id in WHERE clause.

Upvotes: 0

poh
poh

Reputation: 390

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')

Upvotes: 13

aw crud
aw crud

Reputation: 8891

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.

Upvotes: 6

Related Questions