SQL Query - Adding NULL results to a SELECT query

Question

This is my first time using Stack Overflow, so I hope I'm asking this question in the right way.

I have 2 SQL queries that I am trying to compare and identify the missing values, although I'm having trouble adding in the fields that are NULL into the second query to identify the missing information. I'll list the queries and then explain what I'm looking for.

Query #1

SELECT C.CustomerId, C.CustomerName, C.StatusId
FROM Customer C
WHERE C.StatusId = 1
ORDER BY C.CustomerName

Query #2

SELECT C.CustomerId, C.CustomerName, C.StatusId, I.AuthorityId
FROM Customer C
    JOIN Identifier I ON I.CustomerId = C.CustomerId
WHERE C.StatusId = 1
    AND I.AuthorityId = 11
ORDER BY C.CustomerName

The first query gives me a list of customers that are active in our system. The second query gives me a list of active customers that have a record where the AuthorityId = 11.

From my list of active customers (results from query #1), there are several customers that are not showing up in the results from my second query because they do not have a record in the Identifier table where the AuthorityId = 11. I would like to generate a list showing all active customers along with the data in the AuthorityId column, but if the active customer does not have a record in the Identifier table where AuthorityId = 11, I would like it to say NULL so that I can clean up the missing data.

I hope this is clear of what I am looking for.

Thanks in advance for the help!

user359040 · Accepted Answer · 2012-02-07 15:56:46Z

2

Try:

SELECT C.CustomerId, C.CustomerName, C.StatusId, I.AuthorityId
FROM Customer C
LEFT JOIN Identifier I ON I.CustomerId = C.CustomerId and I.AuthorityId = 11
WHERE C.StatusId = 1
ORDER BY C.CustomerName

answered Feb 7, 2012 at 15:56

user359040

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

1 Comment

Brandon Over a year ago

Thanks! Although these answers were all similar, I used this one and it worked perfectly. Thanks for the quick responses!

John Woo · Accepted Answer · 2012-02-07 15:55:18Z

1

just change JOIN into LEFT JOIN

SELECT C.CustomerId, C.CustomerName, C.StatusId, I.AuthorityId
FROM Customer C
    LEFT JOIN Identifier I ON I.CustomerId = C.CustomerId
WHERE C.StatusId = 1
    AND I.AuthorityId = 11
ORDER BY C.CustomerName

UPDATE

if you want to change (NULL) value into NULL or whatever word you want to replace, you can you COALESCE

SELECT C.CustomerId, C.CustomerName, C.StatusId, COALESCE(I.AuthorityId, 'whatever')
FROM Customer C
    LEFT JOIN Identifier I ON I.CustomerId = C.CustomerId
WHERE C.StatusId = 1
    AND I.AuthorityId = 11
ORDER BY C.CustomerName

answered Feb 7, 2012 at 15:55

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

GarethD Over a year ago

Isn't your left join cancelled out by "AND AuthorityId = 11"? The result set will not contain any customers with no record in Identifier, this needs to be included in the join statement, or accounted for using ISNULL or similar.

Mithrandir · Accepted Answer · 2012-02-07 17:03:11Z

0

SELECT C.CustomerId, C.CustomerName, C.StatusId, I.AuthorityId
FROM Customer C
    LEFT OUTER JOIN Identifier I ON 
    (I.CustomerId = C.CustomerId) 
    AND (I.AuthorityId = 11)
WHERE  (C.StatusId = 1)
ORDER BY C.CustomerName

A left outer join or left join is what you want here.

edited Feb 7, 2012 at 17:03

answered Feb 7, 2012 at 15:57

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

1 Comment

GarethD Over a year ago

This will return customers where C.StatusID <> 1 because you are including this in the left outer join!

Collectives™ on Stack Overflow

SQL Query - Adding NULL results to a SELECT query

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related