Login

IN

The IN operator allows you to specify a list of possible values in the WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Example

Return all customers FROM 'Germany', France' or 'UK':

SELECT * FROM customers
WHERE country IN ('Germany', 'France', 'UK');

NOT IN

By using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the list.

Example

Return all customers that are NOT from 'Germany', France' or 'UK':

SELECT * FROM customers
WHERE country NOT IN ('Germany', 'France', 'UK');

IN (SELECT)

You can also us a SELECT statement inside the parenthesis to return all records that are in the result of the SELECT statement.

Example

Return all customers that have an order in the orders table:

SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

NOT IN (SELECT)

The result in the example above returned 89 records, that means that there are 2 customers that haven't placed any orders.

Let us check if that is correct, by using the NOT IN operator.

Example

Return all customers that have NOT placed any orders in the orders table:

SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Select all customers where the country field is one of the the values in the following list:

('Norway', 'Sweden', 'Denmark')

SELECT * FROM customers
WHERE country ;

Login
ADS CODE