CODEWITHSUNDEEP
sql-server-2008
My2ndLovE
My2ndLovE

Reputation: 397

MS SQL coonditional check null value

I need to select records from tbl_A based on A_Class field from tbl_B as below

SELECT * FROM tbl_A
WHERE A_Class IN (SELECT A_Class FROM tbl_B)

However, the A_Class might contains NULL value

I need to add in another condition

If A_Class contains NULL value, then

WHERE A_Class IN (SELECT A_Class FROM tbl_B) OR A_Class IS NULL

If no null value, the condition to check NULL need to be ignore/remove

So my question is, is it possible to make it become a single query?

Upvotes: 0

Views: 75

Answers (2)

Alex Peshik
Alex Peshik

Reputation: 1515

You can try this code if there aren't blank values (I mean '') in A_Class:

SELECT * FROM #tbl_A
WHERE exists
     (SELECT * FROM #tbl_B
     where isnull(#tbl_A.A_Class,'')=isnull(#tbl_B.A_Class,''))

If blank values are present, you can use even this: isnull(#tbl_A.A_Class,'NULL')=isnull(#tbl_B.A_Class,'NULL') with typed phrase 'NULL'.

Upvotes: 1

Sergi
Sergi

Reputation: 577

If you want to show all the records without null values: SELECT a.* FROM tbl_A a INNER JOIN tbl_B b ON a.A_Class = b.A_Class WHERE a.A_Class IS NOT NULL;

If you want to show all the records without null values from tbl_A and all of them that are in tbl_B: SELECT a.* FROM tbl_A a LEFT JOIN tbl_B b ON a.A_Class = b.A_Class WHERE a.A_Class IS NOT NULL;

If you want to show all the records without null values from tbl_B and all of them that are in tbl_A: SELECT a.* FROM tbl_A a RIGHT JOIN tbl_B b ON a.A_Class = b.A_Class WHERE b.A_Class IS NOT NULL;

Upvotes: 0

Related Questions