[NOT] IN

This function checks whether the specified argument is present in the result of a subquery or not.

Syntax

... expr [NOT] IN (subquery)

Arguments

expr: The values that should be compared with the results of a subquery.

---

subquery: This clause defines a subquery that returns a list of values with which expr can be compared.

Usagenotes

Please note that NULL != NULL. The return value of the function with argument NULL is NULL. It does not matter whether the results of the subquery or the value list contain NULL values or not. NULL

See Examples.

Syntactically, [NOT] IN should be treated as an operator and not as a function. See also Subqueries.

Examples

CREATE TABLE mkw_doku.val_t (col STRING);
INSERT INTO mkw_doku.val_t VALUES('First'); 
INSERT INTO mkw_doku.val_t VALUES('Second'); 
INSERT INTO mkw_doku.val_t VALUES(NULL); 
INSERT INTO mkw_doku.val_t VALUES('Third'); 
CREATE TABLE mkw_doku.exp_t (col STRING);
INSERT INTO mkw_doku.exp_t VALUES('First'); 
INSERT INTO mkw_doku.exp_t VALUES('Fourth'); 
INSERT INTO mkw_doku.exp_t VALUES('Fifth'); 
INSERT INTO mkw_doku.exp_t VALUES(NULL); 

SELECT * FROM mkw_doku.val_t;

+--------+
| COL    |
+--------+
| First  |
| Second |
| NULL   |
| Third  |
+--------+

SELECT * FROM mkw_doku.exp_t;

+--------+
| COL    |
+--------+
| First  |
| Fourth |
| Fifth  |
| NULL   |
+--------+

Select all rows from the table exp_t where the column col contains values that are also present in the column col in the table val_t:

SELECT * FROM mkw_doku.exp_t et WHERE et.col IN (SELECT col FROM mkw_doku.val_t);

+-------+
| COL   |
+-------+
| First |
+-------+

The row with col = NULL is not returned.

Please also note that the following query does not return any results due to the NULL values in the column col of the table val_t:

SELECT * FROM mkw_doku.exp_t et 
         WHERE et.col NOT IN (SELECT col FROM mkw_doku.val_t);

! Query returned no results
The result set is empty

If we exclude NULL values from the results of the subquery, the following query returns the rows from the table exp_t where the column col contains values that are not present in the column col in the table val_t:

SELECT * FROM mkw_doku.exp_t et 
         WHERE et.col NOT IN (SELECT col FROM mkw_doku.val_t WHERE col IS NOT NULL);

+--------+
| COL    |
+--------+
| Fourth |
| Fifth  |
+--------+