Learn SQL with PostgreSQL - Subqueries, Set Operations & Expressions

#Introduction

Welcome to episode 7 of the Learn SQL with PostgreSQL series! In episode 6, we learned to combine tables with joins. Now we'll expand our SQL vocabulary with three important capabilities: subqueries (queries within queries), conditional expressions (branching logic in SQL), and set operations (mathematical set operations between query results).

In this episode, we'll cover subqueries in the WHERE clause with the operators IN, EXISTS, ANY, and ALL, subqueries in the FROM clause (derived tables) and SELECT, the CASE WHEN branching logic, the NULL-handling functions COALESCE and NULLIF, and then the set operations UNION, INTERSECT, and EXCEPT.

#Subqueries: Queries Within Queries

A subquery is a SELECT query nested inside another query. It can appear in various clauses, and its result can be a single value, a list of values, or a full table.

#Subqueries in the WHERE Clause

The most common position: a subquery as a filter. The operators often used:

• IN: checks whether a value is included in the subquery result.
• EXISTS / NOT EXISTS: checks whether the subquery returns at least one row.
• ANY: compares against "at least one" value of the subquery result.
• ALL: compares against "all" values of the subquery result.

SELECT email, full_name
FROM users
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE total > 1000000
);

EXISTS is an alternative that is often more efficient because it stops at the first matching row:

SELECT email, full_name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = u.id
      AND o.total > 1000000
);

Notice the correlation: the subquery above references u.id from the outer query. This is called a correlated subquery — the subquery is evaluated for each row of the outer query.

#Subqueries in the FROM Clause (Derived Tables)

A subquery in FROM produces a temporary table called a derived table. It must be given an alias, and its columns can be used like a regular table:

SELECT email, total_belanja
FROM (
    SELECT
        customer_id,
        SUM(total) AS total_belanja
    FROM orders
    GROUP BY customer_id
) AS ringkasan
JOIN users u ON u.id = ringkasan.customer_id
ORDER BY total_belanja DESC;

#Subqueries in the SELECT Clause

A subquery can also become a result column. It must return a single value (scalar subquery):

SELECT
    email,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = u.id) AS jumlah_order
FROM users u
ORDER BY jumlah_order DESC;

#Conditional Expressions

#CASE WHEN: Branching Logic in SQL

CASE WHEN is SQL's way of doing if-then-else. It returns a different value based on a condition:

SELECT
    name,
    price,
    CASE
        WHEN price > 1000000 THEN 'mahal'
        WHEN price > 100000 THEN 'menengah'
        ELSE 'terjangkau'
    END AS kategori_harga
FROM products;

The CASE value WHEN form can also check value equality concisely:

SELECT
    order_id,
    status,
    CASE status
        WHEN 'new' THEN 'pesanan baru'
        WHEN 'paid' THEN 'sudah dibayar'
        ELSE 'status lain'
    END AS deskripsi_status
FROM orders;

#COALESCE and NULLIF

The two most useful functions for handling NULL:

• COALESCE(value, default): returns the first non-NULL value from the argument list.
• NULLIF(a, b): returns NULL if a = b, otherwise returns a. Useful for avoiding division by zero.

SELECT
    email,
    COALESCE(phone, 'belum diisi') AS telepon,
    NULLIF(age, 0) AS usia
FROM users;

#Set Operations

Set operations combine the results of multiple queries — unlike joins, which combine columns. The result is rows arranged vertically. The requirement: the number and types of columns of each query must be compatible.

#UNION vs UNION ALL

UNION combines the results of two queries and removes duplicates. UNION ALL combines without removing duplicates.

SELECT customer_id FROM orders_2025
UNION
SELECT customer_id FROM orders_2026;
 
SELECT customer_id FROM orders_2025
UNION ALL
SELECT customer_id FROM orders_2026;

The first query: the list of customers who ordered in 2025 or 2026, unique. The second query: all rows — customers who ordered in both years appear twice. UNION ALL is faster because it doesn't need the deduplication process, so use it if duplicates are allowed.

#INTERSECT and EXCEPT

• INTERSECT: rows that appear in both queries.
• EXCEPT: rows that appear in the first query but not in the second.

SELECT customer_id FROM orders_2026
INTERSECT
SELECT customer_id FROM orders_2025;
 
SELECT customer_id FROM customers_2026
EXCEPT
SELECT customer_id FROM customers_2025;

The first query: customers who ordered in both years. The second query: new customers (present in 2026 but not in 2025).

#Closing

Key takeaways:

• Subqueries can appear in WHERE, FROM, and SELECT — each has its own result rules (value, table, or scalar).
• EXISTS is often more efficient for correlated subqueries.
• CASE WHEN is if-then-else in SQL; COALESCE and NULLIF are your weapons against NULL.
• UNION ALL is faster than UNION when deduplication isn't needed.
• INTERSECT and EXCEPT are powerful tools for analyzing data across periods.

In episode 8, we'll look at the side of PostgreSQL that makes it called a hybrid database: Advanced Data Types: JSONB & Array Support — from the difference between JSON and JSONB, JSON query operators like -> and @>, the manipulation function jsonb_set, to the array data type with the operators ANY, @>, and UNNEST. Get ready, because this is where PostgreSQL starts to feel special.