what what - 4 months ago 7
SQL Question

Doing a LEFT JOIN, the matching value is lost from the result when it does not exist in the second table

When I left join two tables like this:

SELECT * FROM a LEFT JOIN b ON a.id = b.id


if there is no matching row in b, then "id" is empty, although it exists in a.

How can I keep that value?

Answer

It is not really empty. That is just how the results are interpreted by the tool you are using. You see, the problem is that the result set has two columns called id, so one is arbitrarily chosen -- and it doesn't seem to be the one you want.

The simplest method is to switch to a using clause:

SELECT *
FROM a LEFT JOIN
     b 
     USING (id);

This returns id only once in the result set and it should have the value in the first table (in this case).