SubQueries and Compound Statements
Sub-Queries
Sub Query is a technique that can be used to pass the value from one Query to another. These are usually not recommended by Database Administrators, as the Sub Queries are usually more than one query within a single query, this doesn’t allow the database to optimize the entire query as there are many involved.
SELECT * FROM account WHERE email='email@domain.com' WHERE account_id = (SELECT id from account WHERE email = 'xyz@domain.com');Transclude of subquery.excalidraw
Using sub-queries is slower than using two normal queries, as it doesn’t allow the database to perform all the performance optimizations to allow for minimal time retrieval, sometimes using sub queries you might not always be able to use all the nice things that the database offers.
Types of Subqueries
| Type | Description | Example |
|---|---|---|
| Scalar | Returns a single value | WHERE id = (SELECT MAX(id) FROM ...) |
| Row | Returns a single row | WHERE (a, b) = (SELECT a, b FROM ... LIMIT 1) |
| Table | Returns multiple rows/columns | FROM (SELECT ... ) AS derived |
| Correlated | References outer query — re-executed per outer row | WHERE EXISTS (SELECT 1 FROM ... WHERE inner.id = outer.id) |
Subquery vs JOIN
In many cases, a subquery can be rewritten as a JOIN for better performance. The optimizer may do this automatically, but not always:
-- Subquery (may be slower)
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'US');
-- Equivalent JOIN (often faster)
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.region = 'US';EXISTS vs IN
For checking existence, EXISTS is often faster than IN because it short-circuits — it stops scanning as soon as the first match is found:
-- EXISTS (stops at first match)
SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);
-- IN (builds full result set first)
SELECT * FROM departments WHERE id IN (SELECT dept_id FROM employees);Subquery Alternatives
Consider these alternatives that are often more readable and performant:
- Common Table Expression (CTE) — Named, reusable, supports recursion
- SQL Views — Persistent named queries for reuse across statements
- Window Functions — Compute aggregates without collapsing rows
Compound Statements
There are statements which do more than one things in one statement for efficiency and Transactions
INSERT INTO fav(
post_id, account, howmuch
)
VALUES( 1,1,1)
RETURNING *;In the above query the keyword RETURNING means that after the operation return all the rows that have been affected, in this manner we do not have to run another query to fetch the updated data.
INSERT … SELECT
Insert rows from a query result into another table:
INSERT INTO archived_orders
SELECT * FROM orders WHERE order_date < '2025-01-01';UPDATE … FROM
Update rows using data from another table (PostgreSQL):
UPDATE orders SET status = 'shipped'
FROM shipments
WHERE orders.id = shipments.order_id AND shipments.shipped_date IS NOT NULL;DELETE … USING
Delete rows based on a join condition (PostgreSQL):
DELETE FROM cart_items
USING orders
WHERE cart_items.order_id = orders.id AND orders.status = 'completed';