Lesson 9 of 14 · 8 min · SQL for applications
SQL in applications: joins, aggregates and subqueries on real schemas
What you will learn
- Join three tables the way an application's data model needs
- Spot and avoid the fan-out trap that double-counts money
- Aggregate first, then join
- Paginate results safely
- Use parameters, not string building, to protect against SQL injection
In simple words
In a real application, one screen often needs data from three or four tables, and the same query runs thousands of times a day with different values typed by users. Two things matter beyond writing a correct query: making sure the numbers are not accidentally counted twice, and making sure a user's input can never change what the query does.
Words to know
- Fan-out: when a join repeats a row several times because it matches several rows on the other side.
- Pagination: returning a long list in pages, such as 20 rows at a time.
- Parameter: a placeholder in a query that the database fills with a value as plain data.
- SQL injection: an attack where input text is treated as SQL code.
Three tables
Add products and order lines to the practice database:
CREATE TABLE products (product_id INT PRIMARY KEY, name VARCHAR(50), price NUMERIC(10,2));
CREATE TABLE order_items (
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id));
INSERT INTO products VALUES (1,'Notebook',100),(2,'Pen pack',50),(3,'Bag',500);
INSERT INTO order_items VALUES
(101,1,1),(101,2,2),(102,2,1),(103,3,1),(104,1,1),(105,2,1),(106,3,1);
Order 101 has two lines, every other order has one. Show each order with the customer and what was bought:
SELECT o.order_id, c.name AS customer, p.name AS product, i.quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
ORDER BY o.order_id, p.name;
Seven rows come back: one per order line. Read joins as a chain: each ON links the new table to one already in the query.
The fan-out trap
Suppose you want total revenue and you join orders to their lines first:
SELECT SUM(o.amount) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;
The answer is 6700.00, but the real total of orders.amount is 5500.00. Order 101 (1200.00) matches two lines, so it was added twice (1200 x 2 + 800 + 1500 + 600 + 900 + 500 = 6700). amount lives at the order level, and the join changed the level to one-row-per-line.
Three safe habits:
- Know the grain. Before joining, say what one row means. Orders: one row per order. After joining lines: one row per line. Sum an order-level column only at order grain.
- Aggregate first, then join. Reduce the many-side to one row per key in a subquery or CTE, then join:
SELECT o.order_id, o.amount, li.items
FROM orders o
JOIN (SELECT order_id, SUM(quantity) AS items
FROM order_items GROUP BY order_id) li ON li.order_id = o.order_id;
- Check totals. Compare a joined total with a simple one-table total whenever a report looks high.
Revenue by product
Line-level sums are safe at line grain, because quantity and price both belong to the line:
SELECT p.name, SUM(i.quantity * p.price) AS revenue
FROM order_items i
JOIN products p ON p.product_id = i.product_id
GROUP BY p.name
ORDER BY revenue DESC;
Notebook: (1 + 1) x 100 = 200; Pen pack: (2 + 1 + 1) x 50 = 200; Bag: (1 + 1) x 500 = 1000. Bag comes first.
Subqueries in application code
Typical uses: "customers who bought a Bag" with EXISTS, or "orders above this customer's own average":
SELECT o.order_id, o.amount
FROM orders o
WHERE o.amount > (SELECT AVG(o2.amount) FROM orders o2 WHERE o2.customer_id = o.customer_id);
Customer 1's average is 833.33, so order 101 (1200.00) qualifies; customer 3's average is 750.00, so order 105 (900.00) qualifies. For large tables, check the plan; a window function (AVG(amount) OVER (PARTITION BY customer_id)) is often faster.
Pagination
Lists in a web app are shown in pages. The simple way:
SELECT order_id, order_date, amount
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 20 OFFSET 40; -- page 3 of 20 per page
Always include a unique tie-breaker (order_id) in ORDER BY, or rows can repeat or disappear between pages. OFFSET gets slower on deep pages because the database still reads and discards all skipped rows. For long lists use keyset pagination: remember the last row seen and ask for the rows after it, WHERE (order_date, order_id) < (:last_date, :last_id), which can use an index.
Parameters and SQL injection
Never build SQL by gluing user text into a string:
query = "SELECT * FROM customers WHERE name = '" + user_input + "'" # dangerous
If user_input is x' OR '1'='1, the query matches every row, and worse inputs can change or delete data. Use parameters, which the database receives separately from the SQL, so input is only ever data:
cursor.execute("SELECT * FROM customers WHERE name = %s", (user_input,)) # Python (psycopg)
PreparedStatement ps = con.prepareStatement("SELECT * FROM customers WHERE name = ?");
ps.setString(1, userInput); // Java (JDBC)
Parameters work for values only. Table names, column names and ORDER BY directions cannot be parameters: validate those against a fixed allow-list in code. ORMs such as JPA use parameters for you, but raw string concatenation inside a "native query" brings the risk back.
Common mistakes
- Summing a parent-level column after joining to a child table.
- Pagination without a unique
ORDER BY. - Building SQL with string concatenation or f-strings.
SELECT *in joins, where duplicate column names confuse the application.- Trusting a total that was never checked against a simple query.
Try it yourself
- Create
productsandorder_items, then list every order line with customer and product names. - Reproduce the fan-out total (6700.00), then fix it by summing
amountfromordersonly. - Compute revenue per product and per customer.
- Write page 2 (size 2) of orders by date with a unique tie-breaker.
- In any language you know, run a query with a parameter instead of a string join.
Check yourself
Why did the joined total show 6700 instead of 5500?
Joining orders to order lines repeated order 101 once per line, so its amount was added twice. The amount belongs at order level but the join made one row per line.
How do you prevent fan-out when you need an order-level total?
Aggregate the many-side to one row per order first (subquery or CTE), then join, or sum the order-level column from the orders table alone.
What does a parameterised query protect against?
SQL injection, because the input is sent as data separate from the SQL text and can never be run as code.
Why add order_id to ORDER BY when paginating?
So the order is fully defined; with ties on the date, rows could otherwise repeat or go missing across pages.
Interview questions
What is SQL injection and how do you prevent it?
It is when untrusted input is concatenated into SQL and changes its meaning. Prevent it with parameterised queries or prepared statements, allow-lists for identifiers, least-privilege database accounts and input validation as an extra layer.
How would you implement pagination for millions of rows?
Use keyset (seek) pagination with an indexed, unique sort key instead of large OFFSET values, which get slower the deeper the page.
A report shows revenue twice the real figure after adding a join. What do you check?
The grain of the joined result. A one-to-many join probably repeated the parent rows; aggregate the child table first or sum from the parent table only.
Summary
- Joins chain tables; always know what one result row represents.
- A one-to-many join can inflate parent-level sums; aggregate first or sum at the right grain.
- Paginate with a unique sort key; use keyset pagination for deep lists.
- Always use parameters, never string concatenation, for user input.
Going deeper
- Joined tables: the full join reference.
- LIMIT and OFFSET: and why ordering matters.
- SQL Injection Prevention Cheat Sheet: OWASP's guidance.
- Aggregate functions: reference.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.