what what - 1 year ago 53
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 Source

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:

     USING (id);

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download