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

问题:

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.

回答1:

Try checking for NULL explicitly:

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

回答2:

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

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

回答3:

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'.

回答4:

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

回答5:

 where column != 'value' or column is null