Select statement with null or not

Question

how to get the data from select Query whete column having null value.

Answer 1

Use Is Null

select * from tblName where clmnName is null    

Answer 2

Do you mean something like:

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

?

Answer 3

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