Select rows where column is null

Question

How do you write a SELECT statement that only returns rows where the value for a certain column is null?

Answer 1

Do you mean something like:

SELECT COLUMN1, COLUMN2 FROM MY_TABLE WHERE COLUMN1 = 'Value' OR COLUMN1 IS NULL

?

Answer 2

I'm not sure if this answers your question, but using the IS NULL construct, you can test whether any given scalar expression is NULL:

SELECT * FROM customers WHERE first_name IS NULL

On MS SQL Server, the ISNULL() function returns the first argument if it's not NULL, otherwise it returns the second. You can effectively use this to make sure a query always yields a value instead of NULL, e.g.:

SELECT ISNULL(column1, 'No value found') FROM mytable WHERE column2 = 23

Other DBMSes have similar functionality available.

If you want to know whether a column can be null (i.e., is defined to be nullable), without querying for actual data, you should look into information_schema.

Answer 3

Use Is Null

select * from tblName where clmnName is null    

Answer 4

You want to know if the column is null

select * from foo where bar is null

If you want to check for some value not equal to something and the column also contains null values you will not get the columns with null in it

does not work:

select * from foo where bar <> 'value'

does work:

select * from foo where bar <> 'value' or bar is null

in Oracle (don't know on other DBMS) some people use this

select * from foo where NVL(bar,'n/a') <> 'value'

if I read the answer from tdammers correctly then in MS SQL Server this is like that

select * from foo where ISNULL(bar,'n/a') <> 'value'

in my opinion it is a bit of a hack and the moment 'value' becomes a variable the statement tends to become buggy if the variable contains 'n/a'.

Answer 5

For some reasons IS NULL may not work with some column data type. I was in need to get all the employees that their English full name is missing, I've used:

SELECT emp_id, Full_Name_Ar, Full_Name_En
FROM employees
WHERE Full_Name_En = '' or Full_Name_En is null

Answer 6

select Column from Table where Column is null;

Answer 7

select * from tableName where columnName is null