Sep 22, 20231 min read

SQL (4): NOT IN

How to select something that is not in the table in SQL? Maybe NOT IN command can help.

Let's look at an example:

SELECT c.name AS Customers FROM Customers c
    WHERE c.id NOT IN (SELECT customerId FROM Orders)

SELECT defines you want to show in the output.

c.name means the name column from table Customers.

AS sets the out column name to Customers.

FROM means retrieving data from Customers table and assigns it to c.

WHERE filters your target data by condition.

c.id NOT IN (SELECT customerId FROM Orders) is the condition, which is used to select the customers who did not appeared in table Orders.

c.id means the id column in table Customers.

NOT IN filters out the customers whose id appeared in the customerId column in table Orders. (so id is the primary key)

For the reason why using (SELECT customerId FROM Orders), it's the syntax rule for NOT IN command in SQL.

It simply means retriving data from customerId column in table Orders.

In this way, we will find the output to be:

+-----------+
| Customers |
+-----------+
| James     |
| Harmony   |
+-----------+

Congratulations on completing this tutorial!

SQL is not that hard if you pay effort.

See you in SQL (5)!

Recent Posts