Join 2 Tables and display everything

Question

I have 2 simple tables:

table a:

id | txt
---+----
0  | aaa
1  | bbb
2  | ccc

table b:

id | tel
---+----
0  | 000
2  | 111

I am trying to join 2 tables like this:

SELECT a.*,b.* 
  FROM a,b 
 WHERE a.id=b.id

It works, but, if there is no entry in the "b" table it wont show anything.

what the sql shows is something like this:

id | txt | tel
---+-----+----
0  | aaa | 000
2  | ccc | 111

I also want to list the "empty" row a.id=1:

id | txt | tel
---+-----+-----
1  | bbb | NULL

Any ideas? Thanks! (SQL is for MS Access / oledb)

Answer 1

In MSAccess query designer, right click on the relationship connection between the two tables, and edit its properties. Modify it to 'show all records from table a'.

Answer 2

You want a left outer join (or a full outer join, if you want rows in which there is no entry in the a table also).

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

Depending the system, the syntax LEFT JOIN may work as well.

Answer 3

This:

SELECT a.*,b.* 
  FROM a, b 
 WHERE a.id = b.id

...is ANSI-89 inner join syntax, and I highly recommend using ANSI-92 syntax instead:

SELECT a.*,
       b.*
  FROM TABLE_A a
  JOIN TABLE_B b ON b.id = a.id

...mostly because ANSI-88 syntax for left joining to tables wasn't consistently implemented on various databases. This will return the results you expect:

   SELECT a.id,
          a.txt,
          b.tel
     FROM TABLE_A a
LEFT JOIN TABLE_B b ON b.id = a.id

A LEFT JOIN means you'll get all the records from TABLE_A in this example, and when there is a matching record (based on the ON criteria) in TABLE_B, the value will be displayed. Otherwise, the TABLE_B column references will return NULL values.

For more information on JOINs, see this page for a great pictorial demonstration of what each one represents and how they compare.