Query not returning NULL values - SQL

Question

I've been trying for awhile by changing my query around but no LUCK! I have tables:

Demographic
( name, gender, nationality, ethnicity etc)
Reference
(ID, Code as varchar, Description varchar)

Basically I add all my values in the reference table like nationality, Gender, ethnicity then I link it to demographics, eg. ID:1 , Code: Gender , Description: Male

So in demographics any male will have value 1 ( the Reference.ID)

I've written this query:

Select Id, fname, Surname
         ,e.Description as Nationality
         ,a.Description as Gender
FROM Demographics d, Reference e, Reference a
WHERE (d.Nationality = e.ID OR d.Nationality IS NULL) AND (d.Gender = a.ID OR 
d.Gender IS NULL)

Without the IS NULL parts it works but it excludes all NULL values. But when I add IS NULL it returns inaccurate values like in Gender column I will have ethnicity values.

Any help will be appreciated.

What results are you expecting here? NULL, in SQL Server, is treated as an unknown value. Thus, an expression like [Column] != 1 will return false, if the value of [Column] is NULL. Also, it's worth noting that NULL = NULL would also return false; as an unknown does not equal an unknown in SQL Server. The only way to compare to a NULL is by using IS NULL and IS NOT NULL. Without any sample or expected data, however, I have no idea what you are expecting here. Forum Etiquette: How to post a T-SQL Question. — Thom A
– Thom A ♦, Commented Feb 20, 2018 at 9:48
Also, things to avoid, DON'T use demlimited lists for your table and use implicit joins in the WHERE clause. JOIN syntax has been around for decades. Use it. — Thom A
– Thom A ♦, Commented Feb 20, 2018 at 9:50
I am using IS NULL for Gender and Nationality. so when Gender row is null for example as in no values was specified it will not return it it will just return gender/nationality columns with values. whilst i have many users with no gender / nationality specified — Warda
– Warda, Commented Feb 20, 2018 at 9:54
Does this answer your question? Mixing implicit and explicit JOINs — philipxy
– philipxy, Commented Apr 21, 2022 at 13:33

Thom A · Accepted Answer · 2018-02-20 09:54:46Z

2

This is quite the guess here, however, I think you're problem is that you're writing SQL like you're in the 1980's. As I said in my comment, JOIN syntax has been around for decades. Stop using delimited lists for your tables and using implicit joins.

Anyway, I think what you need is a LEFT JOIN:

SELECT d.Id, d.fname, d.Surname,
       n.[Description] AS Nationality,
       g.[Description] AS Gender
FROM Demographics d
     LEFT JOIN Reference n ON d.Nationality = n.ID
     LEFT JOIN Reference g ON d.Gender = g.ID;

If this isn't correct, then sample and expected results are going to be needed.

answered Feb 20, 2018 at 9:54

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Warda Over a year ago

thanks for your answers! I am in uni and we are being taught 'Where' clause rather than Join. So clearly its still around!

Thom A Over a year ago

@Warda it's not. it's really not. you won't see anyone with any kind of reputation in SQL ever recommend, or teach, implicit joins. If your tutor is teaching your WHERE implicit joins then they, are teaching it wrong. They are awful. I would honestly be concerned if that's the method they think is correct. I would strongly suggest asking them why they aren't covered JOIN syntax, which is part of the ANSI syntax.

IVNSTN · Accepted Answer · 2018-02-20 09:54:22Z

0

Switch to non-deprecated join style and everithing will go right way:

Select Id, fname, Surname
         ,e.Description as Nationality
         ,a.Description as Gender
FROM Demographics d
LEFT JOIN Reference e 
  ON e.ID = d.Nationality
LEFT JOIN  Reference a
  ON a.ID = d.Gender

answered Feb 20, 2018 at 9:54

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Comments

Abdul Rasheed · Accepted Answer · 2018-02-20 09:54:30Z

0

You need a LEFT JOIN here

Select Id, fname, Surname
         ,e.Description as Nationality
         ,a.Description as Gender
FROM Demographics d
    LEFT JOIN Reference e ON d.Nationality = e.ID
    LEFT JOIN Reference a ON d.Gender = a.ID

answered Feb 20, 2018 at 9:54

Abdul Rasheed

6,7794 gold badges36 silver badges50 bronze badges

Comments

Bhavin Gosai · Accepted Answer · 2018-02-20 10:34:40Z

Try following query:

    Select Id, fname, Surname
             ,e.Description as Nationality
             ,a.Description as Gender
    FROM Demographics d, Reference e, Reference a
    WHERE   (d.Nationality = e.ID OR (e.ID IS NULL AND d.Nationality IS NULL)) 
    AND     (d.Gender = a.ID OR (a.ID IS NULL AND d.Gender IS NULL))

I have tweaked your query a little. I think you are on the right path, just missed a little, while comparing with NULL.

When you check for one of the operand for nullability (i.e. IS NULL) and miss the other one, query give you all on that missing other side.

Check out this Microsoft TechNet article for further learning: NULL Comparison Search Conditions - TechNet - Microsoft

Collectives™ on Stack Overflow

Query not returning NULL values - SQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related