Joining Multiple Tables

Lesson content

Joining Multiple Tables Joining Multiple Tables is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly. Example SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id; Key point Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use. Real-life example An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist. Advanced example WITH customer_totals AS ( SELECT customer_id, SUM(total) AS spent FROM orders WHERE status = 'paid' GROUP BY customer_id ) SELECT c.id, c.name, t.spent FROM customers c JOIN customer_totals t ON t.customer_id = c.id WHERE t.spent > 1000 ORDER BY t.spent DESC; Expected result The query returns only the intended rows and columns, with deterministic ordering where order matters. Production check Test with empty, duplicate, null, and boundary values. Use a transaction for related writes. Inspect the execution plan before adding an index. Use parameterized queries for application input. Continue with the PicoStore database This lesson reuses picostore . Relevant tables: customers, products, orders, order_items . Keep the starter rows from the Introduction lesson so results remain comparable. Another practical example SELECT c.name, COUNT(o.order_id) AS order_count, COALESCE(SUM(o.total), 0) AS lifetime_value FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name ORDER BY lifetime_value DESC; Check the result Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct. Easy example Start with a small customer table and retrieve active customers in a predictable order. SELECT customer_id, name, email FROM customers WHERE status = 'active' ORDER BY name; How to verify the easy example Run it with representative input. Confirm the expected output. Try one missing, invalid, or boundary value. Advanced example Use a CTE and a window function to rank customer revenue while keeping the query readable and testable. WITH customer_revenue AS ( SELECT customer_id, SUM(total_amount) AS revenue FROM orders WHERE order_status = 'completed' GROUP BY customer_id ) SELECT customer_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM customer_revenue ORDER BY revenue_rank, customer_id; Advanced review Explain the tradeoffs and assumptions. Test failure, scale, security, and recovery behavior. Capture evidence from tests, execution plans, logs, or review output. Additional practical guidance Joining Multiple Tables: MySQL and PostgreSQL Model the rule with keys and constraints instead of relying only on application code. MySQL and PostgreSQL support the core relational design, but generated-column, check-constraint, and alteration details can differ by server version. Required verification Run the simple case. Test a NULL, duplicate, empty, or boundary case where relevant. Confirm the affected rows or query result. Use EXPLAIN for performance-sensitive queries.