What statement could you use in the WHERE clause to select all the rows in a table where no price is

[sameera liyanage]

1)	What statement could you use in the WHERE clause to select all the rows in a table where no price is defined? 
•	WHERE price IS NULL 
•	WHERE price != 0  
•	WHERE price IS < 0 
•	WHERE price IS NOT NULL

I answered 1 answer, but it is wrong.

[Rajkamal Pillai]

[edited]

[Campbell Ritchie]

You should tell us what you think, then we'll tell you whether we agree. Also please say where the question comes from, to avoid copyright and similar problems.

[sameera liyanage]

this question come in local exam.

[Madhan Sundararajan Devaki]

@Aruna your answer is correct.

[Abhishek Ralhan]

You can also try to use all these combinations.
Actually it depends upon the term "where no price is defined". Whatever this term means, the answer lies in that meaning only.

[chris webster]

1)	What statement could you use in the WHERE clause to select all the rows in a table where no price is defined? 
•	WHERE price IS NULL   /* Returns rows where there is no value at all for price i.e. it is a true NULL or undefined value */
•	WHERE price != 0  /* Does what it says i.e. returns values where price is not 0. */
•	WHERE price IS < 0 /* Does what it says i.e. returns rows where price < 0. */
•	WHERE price IS NOT NULL /* The opposite of WHERE price IS NULL above.  */

Also, remember that NULL is not equal to anything (even NULL) so it always fails any comparison with a value. So a NULL value in your table column will return false for any comparisons, except when you use the operators "IS NULL" (returns true if value is NULL) or "IS NOT NULL" (returns true if value is anything except NULL).

Depending on your database, you can also use the NVL() function to handle NULL values in your comparisons etc. NVL(input, output) means that if the input value IS NULL, then the function should return the output value instead, otherwise it just returns the input value. For example:

WHERE NVL(price, 0) = 0

This will return the rows where price is a non-NULL value and equal to 0, or where price is NULL (because the NVL function converts the NULLs to 0 before comparing them).