2

I have two tables as the following

table1
main_id main_val main_sub main_pk
1             A   NULL     3
3             A    1       3

table2

col_id  col_val
1           A

select table1.main_pk from table1 ,table2 WHERE
table1.main_id = table2.col_id
and table1.main_val = table2.col_val
and table1.main_sub = null

Am expecting the above query to select the first row in table 1 as main_sub is null and the other two columns matches. But it does not. I am just learning SQL basics so am not sure where am going wrong. Please help

Ramki
  • 389
  • 1
  • 9
  • Possible duplicate of [SQL is null and = null](https://stackoverflow.com/questions/9581745/sql-is-null-and-null) – tima Aug 25 '17 at 14:53

2 Answers2

3

I didn't fully understand the question but I think this is what you want:

SELECT * from table1 INNER JOIN table2 on table1.main_val=table2.main_val WHERE table1.main_sub IS NULL

If not I think it directs you in the right path

Carlos Alves Jorge
  • 1,919
  • 1
  • 13
  • 29
  • Yes - the key piece here is using `is null` rather than comparing `null` for equality, which doesn't work. – gcbenison Aug 25 '17 at 14:54
2

SQL is a little weird about NULL values. Use the condition 

....
table1.main_sub IS NULL

for testing for a null specifically. The idea is that NULL is supposed to be interpreted as nothing, so nothing -equals- nothing can't ever be true because you can't compare something that doesn't exist.

yxre
  • 3,576
  • 21
  • 20