Join 2 Tables and display everything

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

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.