0

Let's say I have 2 tables, one with 3 fields (id, name, num), the other has two (id, num).

t1 :

1       | assassin  | #1
1       | assassin  | #2  
2       | vampire   | (null) 
3       | zombie    | #1
3       | zombie    | #2
(null)  | zombie    | #3
4       | wizard    | (null)

t2 :

1       | #1
1       | #2  
2       | (null)
3       | #1
3       | #2
(null)  | #3
4       | (null)

I tried to join these tables:

SELECT      t2.id, t1.name, t2.num 
FROM        t1 
INNER JOIN  t2  ON  t2.id = t1.id 
                AND t2.num = t1.num

When I execute this statement, the table shows only the records that has no null values. Why? What's the correct way to join these 2 tables? Is it proper to inner join tables on two conditions? (am using vb.net & ms access btw).

Grahamvs
  • 669
  • 6
  • 16
Nerdsworth
  • 51
  • 1
  • 6
  • 1
    null means unknown, so they will not match in a join. if you want them to match you need to use the expression `is null`, but you also need to decide whether null on one side matches everything on the other side or only nulls on the other side. – Charles Bretana May 06 '14 at 15:55

2 Answers2

1

Try using Left Join instead of Inner Join.

The Inner Join only returns rows that are joined.

The Left Join returns all rows from the "left" table, regardless if they join onto the other table.

Grahamvs
  • 669
  • 6
  • 16
1

You cannot use an inner join on null values because null doesn't match itself (null = null returns false, IOW). You need to use an outer join instead.

See this SQL Server answer on joining with nulls

Community
  • 1
  • 1
alroc
  • 27,574
  • 6
  • 51
  • 97