CODEWITHSUNDEEP
t-sql
Val
Val

Reputation: 1822

T-SQL: select rows not equal to a value, including nulls

How do I select rows which don't equal a value and also include nulls in the returned data? I've tried:

SET ANSI_NULLS OFF
SELECT TOP 30 FROM Mails
WHERE assignedByTeam <> 'team01'

I want to return rows which don't have 'team01' in column assignedByTeam but I also want results containing nulls. Unfortunately, the above code doesn't work (doesn't return the nulls).

I'm using MS SQL Server 2008 Express.

Upvotes: 34

Views: 54375

Answers (5)

Bryce
Bryce

Reputation: 714

SELECT TOP 30 FROM Mails
WHERE ISNULL(AssignedByTeam,'') <> 'team01'

I saw a coalesce statement version but ISNULL() is more efficient.

Upvotes: 23

drume
drume

Reputation: 121

When you have a lot of conditions, typing everything twice stinks. Here are two better alternatives:

SELECT TOP 30 FROM Mails
WHERE COALESCE(assignedByTeam,'') <> 'team01'

The COALESCE operator returns the first non-null value in the list. If assignedByTeam is NOT null, it will compare the assignedByTeam value to 'team01'. But if assignedByTeam IS null, it will compare a blank '' to 'team01'. It's basically shorthand for the following:

SELECT TOP 30 FROM Mails
WHERE (CASE WHEN assignedByTeam IS NULL THEN '' ELSE assignedByTeam END) <> 'team01'

The second way is to make your condition conditional, for example:

SELECT TOP 30 FROM Mails
WHERE 1 = CASE WHEN assignedByTeam = 'team01' THEN 0 ELSE 1 END

In this example, the ELSE value will include all null rows, since they aren't equal to 'team01'.

Upvotes: 12

Mark Byers
Mark Byers

Reputation: 838096

Try checking for NULL explicitly:

SELECT TOP 30 col1, col2, ..., coln
FROM Mails
WHERE (assignedByTeam <> 'team01' OR assignedByTeam IS NULL)

Upvotes: 36

Carl R
Carl R

Reputation: 8214

SELECT TOP 30 FROM Mails
WHERE assignedByTeam <> 'team01'
OR assignedByTeam is null

Upvotes: 4

juergen d
juergen d

Reputation: 204756

 where column != 'value' or column is null

Upvotes: 2

Related Questions