Section 1 IN Operator

IN is an operator that tells the database whether a value exists in a specified list of values.

test_expression [ NOT ] IN   
    ( subquery | expression [ ,...n ]  
    )   

• test_expression is any valid expression
• subquery has a result set of one column. This column must have the same data type as test_expression.
• expression[ ,… n ] is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

An example of this in practice is if we have multiple OR conditions.

SELECT * FROM customer_transactions
WHERE customer_country = 'United States'
   OR customer_country = 'Canada'
   OR customer_country = 'Mexico'
   OR customer_country = 'United Kingdom'
   OR customer_country = 'Australia';

Instead of writing multiple OR statements, we can simply use an IN statement.

SELECT * FROM customer_transactions
WHERE customer_country IN ('United States', 'Canada', 'Mexico', 
                           'United Kingdom', 'Australia');

This makes the syntax easier to read and edit (such as if we need to add or remove a value from the IN statement).

Section 2 Exists Operator

EXISTS is an operator that checks if a subquery contains any of the rows. The EXISTS clause is used to compare two tables and check if your table has values that exist in the other table. There is also a NOT EXISTS clause, which checks for those items not in the other reference table.

SELECT COLUMN1
FROM TABLE1
WHERE EXISTS (
     SELECT 1
     FROM TABLE2
     WHERE COLUMN2 = TABLE1.COLUMN2 )

SELECT 1 is used because EXISTS doesn’t care what the subquery selects. You could also use *SELECT *.

The most important part of this entire subquery is WHERE COLUMN2 = TABLE1.COLUMN2. This line refers to the column from the outer table.

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.manager_id = e.employee_id
)

If EXISTS evaluated to True, it keeps the row. If it evaluates to False, it does not keep the row in the output. In the example above, we are returning all the employees that are also considered managers.

Section 3 IN versus EXISTS

There is a difference between these two operators and thus, it is important to know which one to use depending on our desired output.

• EXISTS efficiently stops scanning when it finds the first matching record. IN continues searching for all values
• EXISTS can compare null values. IN cannot compare null values
• Database will evaluate IN subquery first. In other words, the IN subquery is loaded into the memory first before the outer query. Thus, if the database is large, IN will have a slower performance.