Select from two outer joined tables where a possible null column

Question

Question similar to this:

select from two tables where linked column can be null

column1tab1 column2tab1 order_number product amount
 xx            yy            123      p1      2
 xx            yy            456      p3      4
 xx            yy            NULL    NULL    NULL
 xx            yy            789      p2      1
 etc...

The poster's output was like above. However, when I add in WHERE product = 'p1', or alternatively WHERE product = NULL both return an empty set. Eventually, I want to have

SELECT            *
FROM              `t1`
  LEFT OUTER JOIN `t2`
  ON              (`t1`.`id` = `t2`.`id2`)
WHERE             `t2`.`product` = NULL
  AND             `t2`.`product` <> 'p1'

Which part am I doing wrong? The join or the where? Or something else?

`product = NULL` would be `product IS NULL`, but beside that a null value automatically makes the second `product <> 'p1'` truthy. — Brad Christie, Jun 25 '12 at 07:48

score 2 · Accepted Answer · edited Jun 25 '12 at 21:13

2

Replace

`t2`.`product` = NULL

with

`t2`.`product` IS NULL

See here

edited Jun 25 '12 at 21:13

Alberto De Caro

5,147
9
47
73

answered Jun 25 '12 at 07:50

Fahim Parkar

30,974
45
160
276

score 1 · Answer 2 · edited Jun 25 '12 at 18:22

1

t2.product = NULL is always false. Use t2.product IS NULL instead.

edited Jun 25 '12 at 18:22

b7kich

4,253
3
28
29

answered Jun 25 '12 at 07:48

quzary

285
1
4

score 0 · Answer 3 · answered Jun 25 '12 at 07:50

0

If t2.product is null it is always different from the string 'p1'. I don't see the need for the and.

answered Jun 25 '12 at 07:50

mihaisimi

1,911
13
15

Select from two outer joined tables where a possible null column

3 Answers3