Getting Started with SQL subqueries. A complete beginner guide with examples.

Introduction

Imagine a family unit (parent(s) and children). Every child has their own unique traits i.e height, weight, voice, complexion etc...
Now children are to parent(s) what subqueries are to a main query.
So in other words subqueries are the main query's child/children.
And when children grow up they become independent humans where they can do things without involving their parent(s) this too happens with certain subqueries meaning some subqueries can run without depending on the outer main query. These type of subqueries are called non-correlated subqueries while those that are fully dependent on the main query are called correlated subqueries.

Subqueries can be associated with clauses; the WHERE, FROM and SELECT clauses. We shall look at each clause with some examples.

Subqueries In the SELECT clause.

The SELECT clause helps us choose specific columns where we want our data to be retrieved and displayed.
This means that by placing a subquery in a SELECT clause you are sort of adding a new column that will display something you want.

For starters, let us create the necessary schema and tables that we will use for the examples. Create a database and run the queries below.

create schema subqueries;
set search_path to subqueries;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(50),
    registration_date DATE,
    membership_status VARCHAR(10)
);
-- CREATE Products table in the subqueries schema
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    supplier VARCHAR(100),
    stock_quantity INT
);
-- CREATE Sales table in the subqueries schema
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity_sold INT,
    sale_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES assignment.customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES assignment.products(product_id)
);

-- CREATE Inventory table in the subqueries schema
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    stock_quantity INT,
    FOREIGN KEY (product_id) REFERENCES assignment.products(product_id)
);

-- Inserting data into customers table
INSERT INTO customers 
(customer_id, first_name, last_name, email, phone_number, registration_date, membership_status) 
VALUES
(1, 'Karen', 'Molina', '[e-mail]', '[phone]', '2020-08-27', 'Bronze'),
(2, 'Elizabeth', 'Archer', '[e-mail]', '778.104.6553', '2023-08-28', 'Silver'),
(3, 'Roberta', 'Massey', '[e-mail]', '[phone]x399', '2024-06-12', 'Bronze'),
(4, 'Jacob', 'Adams', '[e-mail]', '246-459-1425x462', '2023-02-10', 'Gold'),
(5, 'Cynthia', 'Lowery', '[e-mail]', '001-279-688-8177x4015', '2020-11-13', 'Silver'),
(6, 'Emily', 'King', '[e-mail]', '[phone]x5900', '2021-05-01', 'Silver'),
(7, 'Linda', 'Larsen', '[e-mail]', '289-050-2028x7673', '2021-08-20', 'Silver'),
(8, 'Angela', 'Hanson', '[e-mail]', '[phone]', '2023-03-17', 'Bronze'),
(9, 'Whitney', 'Wilson', '[e-mail]', '001-594-317-6656', '2024-01-27', 'Bronze'),
(10, 'Angela', 'Atkins', '[e-mail]', '344.217.5788', '2025-02-05', 'Silver'),
(11, 'Gary', 'Lucero', '[e-mail]', '001-842-595-7853', '2024-10-08', 'Silver'),
(12, 'Matthew', 'Romero', '[e-mail]', '556.328.91896', '2022-04-07', 'Bronze'),
(13, 'Ronald', 'Thompson', '[e-mail]', '298-487-2483', '2023-07-31', 'Bronze'),
(14, 'Suzanne', 'Anderson', '[e-mail]', '[phone]', '2023-11-02', 'Bronze'),
(15, 'Mary', 'Kelly', '[e-mail]', '(845)934-9x286', '2021-01-20', 'Bronze'),
(16, 'John', 'George', '[e-mail]', '[phone]', '2022-05-17', 'Bronze'),
(17, 'James', 'Rodriguez', '[e-mail]', '8826047658', '2022-11-25', 'Gold'),
(18, 'Steven', 'Burnett', '[e-mail]', '[phone]x1246', '2020-01-28', 'Gold'),
(19, 'Jonathan', 'White', '[e-mail]', '755-979-1934x772', '2022-02-06', 'Bronze'),
(20, 'Christopher', 'Santiago', '[e-mail]', '118-589-6973x058', '2021-10-16', 'Silver'),
(21, 'John', 'Diaz', '[e-mail]', '369.915.4337', '2022-09-17', 'Gold'),
(22, 'Curtis', 'Rose', '[e-mail]', '[phone]', '2021-12-14', 'Bronze'),
(23, 'Charles', 'Hughes', '[e-mail]', '[phone]x8994', '2024-07-29', 'Silver'),
(24, 'Sarah', 'Cooke', '[e-mail]', '[phone]x56741', '2024-12-15', 'Bronze'),
(25, 'Luis', 'Harrison', '[e-mail]', '516.509.9493', '2021-08-19', 'Silver'),
(26, 'Annette', 'Greene', '[e-mail]', '[phone]x1078', '2025-04-12', 'Bronze'),
(27, 'Melissa', 'Jacobson', '[e-mail]', '562-245-7784x4729', '2023-04-28', 'Bronze'),
(28, 'Julie', 'Gardner', '[e-mail]', '[phone]x188', '2024-03-31', 'Gold'),
(29, 'Margaret', 'Taylor', '[e-mail]', '[phone]x297', '2021-09-06', 'Bronze'),
(30, 'Erika', 'Mckee', '[e-mail]', '[phone]', '2021-05-25', 'Silver'),
(31, 'Donna', 'Whitney', '[e-mail]', '7086491657', '2022-08-07', 'Gold'),
(32, 'Kristina', 'Wade', '[e-mail]', '603-604-2831x303', '2024-03-16', 'Silver'),
(33, 'Joshua', 'Green', '[e-mail]', '988-232-8285x00933', '2024-05-14', 'Silver'),
(34, 'John', 'Leblanc', '[e-mail]', '229.016.2527x20209', '2022-12-24', 'Silver'),
(35, 'Nicholas', 'Campbell', '[e-mail]', '[phone]', '2022-06-06', 'Gold'),
(36, 'Christopher', 'Hicks', '[e-mail]', '884.881.7758', '2021-04-03', 'Silver'),
(37, 'Craig', 'Miller', '[e-mail]', '390-328-7286x021', '2024-04-30', 'Silver'),
(38, 'Jennifer', 'Bailey', '[e-mail]', '001-992-011-9250', '2022-09-07', 'Silver'),
(39, 'Emma', 'Davis', '[e-mail]', '911.706.3025', '2021-06-04', 'Gold'),
(40, 'Michael', 'Wilson', '[e-mail]', '462.021.3233', '2025-01-14', 'Bronze'),
(41, 'Sarah', 'Church', '[e-mail]', '[phone]x61868', '2021-03-14', 'Silver'),
(42, 'Carolyn', 'Stevenson', '[e-mail]', '040.179.1155', '2024-07-26', 'Silver'),
(43, 'Sarah', 'Cole', '[e-mail]', '481-651-5206x4800', '2024-07-27', 'Silver'),
(44, 'Jeremiah', 'Lozano', '[e-mail]', '846-327-7426', '2023-01-02', 'Bronze'),
(45, 'Leslie', 'Boyd', '[e-mail]', '[phone]', '2022-10-22', 'Silver'),
(46, 'Carrie', 'Anderson', '[e-mail]', '[phone]x6149', '2024-08-23', 'Gold'),
(47, 'Jared', 'Davis', '[e-mail]', '001-069-544-8807x2397', '2022-08-29', 'Bronze'),
(48, 'James', 'Soto', '[e-mail]', '129.857.8193x421', '2023-01-27', 'Gold'),
(49, 'Cody', 'Kline', '[e-mail]', '[phone]x7998', '2022-06-28', 'Bronze'),
(50, 'Jennifer', 'Perkins', '[e-mail]', '762.009.1882', '2020-10-19', 'Silver');`

`select * from customers;`

-- Inserting data into products table
`INSERT INTO products 
(product_id, product_name, category, price, supplier, stock_quantity) 
VALUES
(1, 'Laptop', 'Electronics', 999.99, 'Dell', 50),
(2, 'Smartphone', 'Electronics', 799.99, 'Samsung', 150),
(3, 'Washing Machine', 'Appliances', 499.99, 'LG', 30),
(4, 'Headphones', 'Accessories', 199.99, 'Sony', 100),
(5, 'Refrigerator', 'Appliances', 1200.00, 'Whirlpool', 40),
(6, 'Smart TV', 'Electronics', 1500.00, 'Samsung', 20),
(7, 'Microwave', 'Appliances', 180.00, 'Panasonic', 75),
(8, 'Blender', 'Appliances', 50.00, 'Ninja', 200),
(9, 'Gaming Console', 'Electronics', 350.00, 'Sony', 60),
(10, 'Wireless Mouse', 'Accessories', 25.00, 'Logitech', 300),
(11, 'Keyboard', 'Accessories', 49.99, 'Logitech', 250),
(12, 'Monitor', 'Electronics', 250.00, 'Acer', 120),
(13, 'External Hard Drive', 'Electronics', 80.00, 'Seagate', 90),
(14, 'Tablet', 'Electronics', 400.00, 'Apple', 70),
(15, 'Smartwatch', 'Electronics', 199.99, 'Apple', 120);

select * from assignment.products;

-- Inserting data into sales table
INSERT INTO sales 
(sale_id, customer_id, product_id, quantity_sold, sale_date, total_amount) 
VALUES
(1, 1, 1, 1, '2023-07-15', 999.99),
(2, 2, 2, 2, '2023-08-20', 1599.98),
(3, 3, 3, 1, '2023-09-10', 499.99),
(4, 4, 4, 3, '2023-07-25', 599.97),
(5, 5, 5, 1, '2023-06-18', 1200.00),
(6, 6, 6, 1, '2023-10-05', 1500.00),
(7, 7, 7, 1, '2023-08-01', 180.00),
(8, 8, 8, 2, '2023-09-02', 100.00),
(9, 9, 9, 1, '2023-10-10', 350.00),
(10, 10, 10, 3, '2023-11-12', 75.00),
(11, 11, 11, 2, '2023-12-01', 100.00),
(12, 12, 12, 1, '2023-12-07', 250.00),
(13, 13, 13, 1, '2024-01-15', 80.00),
(14, 14, 14, 1, '2024-02-05', 400.00),
(15, 15, 15, 1, '2024-01-05', 199.99);

-- Inserting data into inventory table
INSERT INTO inventory 
(product_id, stock_quantity) 
VALUES
(1, 50),
(2, 150),
(3, 30),
(4, 100),
(5, 40),
(6, 20),
(7, 75),
(8, 200),
(9, 60),
(10, 300),
(11, 250),
(12, 120),
(13, 90),
(14, 70),
(15, 120);

Example
Show how many customers bought each product

select product_name, (select count(*) from sales s
where s.product_id = p.product_id) as no_of_customers
from products p;

Explanation: The outer query displays the names of employees while the inner query counts total projects per employee.
A new column called total_projects is created and each row from outer query is calculated separately to give an answer in the new column.

Subqueries in the WHERE clause

WHERE clause is used for filtering results so that only those that are required are displayed.
In simple terms this subquery acts as a filter.
Example
Show products that are priced higher than the average price of all products

select product_name from  products
where price > (select avg(price) from products);

Explanation: The outer query displays product names from products table while the inner query only displays average price. Now WHERE in this case makes the outer query to only display names of products of whose price is more than the average price of all products. NOTE that no new column was created in this case.

WHERE clause can be used with IN, EXIST or NOT EXISTS

WHERE plus IN

Example

select product_name from products
where product_id in (select product_id from sales 
where price > 500);

Explanation: This is like a join since the two queries are referencing two different tables i.e The outer query provides details from the products table while the inner query provides details from the sales table. However it is important to note that the common column is the product_id column. So we are selecting product IDs in the sales table and comparing to those in product tables and checking which meets the criteria stated (i.e price > 500)

WHERE plus EXISTS or NOT EXISTS

The EXISTS clause just like a filter only displays things present inside a certain category or categories with at least something inside them.
From our database say you want to show product categories where at least one product was sold. So we link the sales table and the products table using the product_id.
So the results will show us the product category for each product sold. In our case every row of a single sale will appear a total of 15 sales and the product categories displayed.

select category from products p
where  exists(select 1
from sales s
where p.product_id = s.product_id);

Note that in order to get single category without duplication, we add the DISTINCT key word as shown below

select distinct category from products p
where exists(select 1
from sales s
where p.product_id = s.product_id);

NOT EXISTS is the opposite of EXISTS so it will only display categories where no product was sold.
For this we can use the example below

select first_name, c.membership_status from customers c
where not exists (select 1 from sales s
where c.customer_id = s.customer_id);

Explanation: This query will display first_name and the membership_status of customers who did not make a single purchase.

Subqueries in the FROM clause

Subqueries in the FROM clause create a temporary table where you can select your data from.
Just like a normal select statement
select column1, column2 from table1
So instead of 'table1', we introduce a subquery there.

Example

select first_name, last_name, total_quantity
from (select customer_id, sum(quantity_sold) as total_quantity
from sales s
group by customer_id) as quantity_summary
join customers c
on c.customer_id = quantity_summary.customer_id
where total_quantity >=2;

Explanation: In the above query, we created a temporary table called quantity_summary in the from clause with two columns (customer_id and total_quantity). This table was derived from the sales table.
So what happens when we run the query is that the outer query selects two columns from the customers table and one column from our derived 'quantity_summary' table.
We use a join to connect the customers table and the new derived table (quantity_summary). NOTE that there must be a common column between the two tables and it is paramount for you to select the common column in the subquery i.e we selected the customer_id in the quantity_summary table.
Remember that the derived table is treated as a completely new entity so if you do not select the customer_id then a join will not work.

Conclusion

Subqueries are important when you need to simplify complex logic, to filter data and to create temporary tables for specific purposes. It saves a lot of time.
Subqueries can appear in the SELECT, FROM and WHERE clause
SELECT - Adds new column (hence it handles every row)
FROM - Creates a temporary derived table (sort of a summary)
WHERE - Displays data that meets certain criteria (a filter)

There you go!! You are all set with subqueries now practice practice practice!