SQL Server, selecting from 2 columns from different tables

Question

I have these columns from 2 tables

Table1           Table2

Code   ID        Code   ID
 A      1         A     1
 B      1         B     1
 C      1         C     1
 D      1
 E      1

My query:

Select 
    a.id, a.code, b.code 
from 
    Table1 a, Table2 b 
where 
    a.id = '1' and a.id = b.id

What I expected

ID   code   code
1     A       A
1     B       B
1     C       C
1     D      NULL
1     E      NULL

What I got

ID   code   code
1     A      A
1     B      A
1     C      A
1     D      A
1     E      A
1     A      B
1     B      B
1     C      B
....

Any ideas? distinct didn't help

Thanks

Answer 1

Well, all the ID's in both tables are 1, so by joining on ID you'll get the cartesian product of both tables.

Instead, you'll need to do a left outer join based on Table1.Code:

Select a.id, a.code, b.code 
from Table1 a LEFT OUTER JOIN Table2 b
on a.code = b.code
where a.id = '1';

Answer 2

You need to do a LEFT OUTER JOIN instead of a Cartesian Product

    SELECT a.Id, a.Code, b.Code FROM Table1 a 
      LEFT OUTER JOIN Table2 b ON a.Code = b.Code
      WHERE a.Id = '1'

A LEFT OUTER JOIN returns all rows from the left-hand side of the join (in this case Table 1) regardless of whether there is a matching record in the table on the right-hand side of the join (in this case Table 2). Where there is no match a NULL is returned for b.Code as per your requirements.

Reference OUTER JOINS