ReadVitamin

As mentioned earlier while you want to limit the rows with the usage of ‘WHERE’ clause at the same time you can also use the IN operator to have multiple values in the ‘WHERE’ clause. This is an alternative use of having multiple OR. Here is the syntax.

SELECT {*|<Col1>}, <Col2>, <Col3>…

FROM <table name>

WHERE <column name> [NOT] IN (<list of values>)

For example, to select employees from a list of department numbers, use the following SQL.

SELECT First_Name, Last_Name

FROM employee

WHERE dept_no IN (210,652,555,871)

Remember that a result set would be returned even if there is only on matching dept_no found. As mentioned before if you have a NULL operator in the list then there would be no rows returned. To find out rows not matching the list of values, prefix the NOT operator before IN as follows;

SELECT First_Name, Last_Name

FROM employee

WHERE dept_no NOT IN (210,652,555,871)

If you want to use another alternative to this, you can go for ANY, but this has to be used with a comparison operator as follows.

SELECT {*|<Col1>}, <Col2>, <Col3>…

FROM <table name>

WHERE <column name> = ANY (<list of values>)

To negate you have to swap the word with ALL and prefix with the NOT operator before the equal sign, i.e. NOT = ALL <list of values>

Usage of NULL and NOT Operator

The word NULL is a reserved word in any SQL. As you know NULL represents nothing, more accurately missing data or empty cell values. A comparison with NULL always returns NULL. To find out NULL values we can either have a condition with a logical operator with two NOT operators or we can use COALESE function.

Usage of NOT operator

It is a wondering truth that sometimes in life, we do NOT want something. And to satisfy this need in SQL, there is a NOT operator provided. Just prefix this before any comparison operator and you will get what you do NOT want. Here is the syntax.

SELECT {*|<Col1>}, <Col2>, <Col3>…

FROM <table name>

WHERE <column name> NOT <operator> <another column name or constant value>

Popularity: 20% [?]