How do you write a SELECT statement that only returns rows where the value for a certain column is null?
-
7Please make your question a bit clearer, it's almost impossible to determine what you're asking for here..Rob– Rob2010-07-30 06:09:38 +00:00Commented Jul 30, 2010 at 6:09
-
2Very inadequate information in your question. You need to show us your database table and columns and make it clearer what you really want to achieve.Choudhury Saadmaan Mahmid– Choudhury Saadmaan Mahmid2014-03-20 08:59:01 +00:00Commented Mar 20, 2014 at 8:59
Add a comment
|
7 Answers
Do you mean something like:
SELECT COLUMN1, COLUMN2 FROM MY_TABLE WHERE COLUMN1 = 'Value' OR COLUMN1 IS NULL
?
Comments
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.
Comments
Use Is Null
select * from tblName where clmnName is null
Comments
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'.
Comments
select Column from Table where Column is null;
1 Comment
Somnath Muluk
Welcome to SO. Code-only answers are not as helpful as answers that have working code plus helpful explanations. In this case, it would be worthwhile explaining this answer to a 5 year old.
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
1 Comment
sonyisda1
That is just searching for records where Full_Name_En is a space...not very helpful in this question dealing with NULL
select * from tableName where columnName is null
1 Comment
Jeremy Caney
Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?