$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Filtering with a subquery #

In the previous lesson, we used a subquery that returned a single value. But a subquery can also return a list of values. When that's the case, we can't use = or > to compare against it — instead, we use IN, which checks whether a value exists anywhere in that list.

Syntax #

SELECT
    column_name
FROM
    table_name
WHERE
    column_name IN (
        SELECT other_column
        FROM other_table
    );

The inner query runs first and produces a list of values. The outer query then keeps only the rows where column_name matches one of those values.

Example #

Suppose we want to find every product that has been ordered at least once. The order_items table records which product_id was included in each order, so we can pull the list of ordered product ids with a subquery:

SELECT
    name
FROM
    products
WHERE
    id IN (
        SELECT product_id
        FROM order_items
    );

The subquery SELECT product_id FROM order_items returns a list of product ids (with duplicates, since a product can be ordered many times). IN doesn't care about duplicates or order — it simply checks whether each product's id appears anywhere in that list.

Exercise #

Write a command that retrieves the first_name of every customer who has placed at least one order. Use a subquery on the orders table with IN to find which id values from customers have a matching order.

  • The result has a first_name column.
  • The result contains the first names of exactly the customers who have placed at least one order (no more, no fewer).
Solution 
SELECT
    first_name
FROM
    customers
WHERE
    id IN (
        SELECT customer_id
        FROM orders
    );
Output will be displayed here