CODEWITHSUNDEEP
sql
Victor
Victor

Reputation: 17097

How to choose a proper filter for an sql join

If I have table A and table B, each with one column:

A:
col1
1
2
3
1

B:
col1
1
1
4

and I want all rows from A and the matching rows from B, only when the column has non null value in both tables, which one should I use?

select * from A left join B on A.col1 = B.col1 and A.col1 is not null AND B.col1 is not null;

select * from A left join B on A.col1 = B.col1 where A.col1 is not null OR B.col1 is not null;

select * from A left join B on A.col1 = B.col1 and (A.col1 is not null OR B.col1 is not null;)

My guess is that the first and the third are the same and will provide the desired output.

Upvotes: 0

Views: 44

Answers (4)

Gayathri
Gayathri

Reputation: 902

Since you want all the rows from A, below query should work: 

select * from A left outer join B on A.col1 = B.col1 where A.col1 is not null and A.col1<>'N/A' and A.col1<>''

http://sqlfiddle.com/#!2/98501/14

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

In a comment you said you are checking for more than nulls in this case I would probaly take thederived table or CTE approach. Dervied table shown below as you did not specify which database backend, so I don't know if you can use CTEs.

select from (select from tablea where test is not null or test <>'' or test<>'N/A') a JOin (select from tableb where test is not null or test <>'' or test<>'N/A')b ON a.col1 = b.col1

Upvotes: 1

Martin K.
Martin K.

Reputation: 1060

You just need

select * from A left join B on A.col1 = B.col1

NULL will never match anything (when not compared with IS NULLand the like), therefore NULL in A won't match anything in B.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460138

If you want to skip null values and you want to link both tables only on existing values you should use an INNER JOIN, the null check is redundant:

SELECT A.* 
FROM A INNER JOIN B ON A.col1 = B.col1

NULL will never match any other value (not even NULL itself), unless the join condition explicitly uses the IS NULL or IS NOT NULL predicates.

Upvotes: 2

Related Questions