JOINs & Complex Queries

IntermediateDatabases
PostgreSQLMySQLEXPLAIN

Objective

Write advanced SQL queries with complex JOINs, subqueries, and CTEs.

Tools & Technologies

  • PostgreSQL
  • MySQL
  • EXPLAIN

Key Commands

WITH cte AS (SELECT ...) SELECT * FROM cte;
SELECT * FROM a LEFT JOIN b ON a.id=b.a_id WHERE b.id IS NULL;
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) FROM events;

Lab Steps

01
INNER vs OUTER JOINs

Compare INNER, LEFT, RIGHT, and FULL OUTER JOIN results.

02
Subqueries

Write correlated and non-correlated subqueries.

03
CTEs

Use Common Table Expressions for readable, recursive queries.

04
Window Functions

Apply ROW_NUMBER, RANK, LAG, LEAD for analytical queries.

Challenges Encountered

  • Cartesian product from missing JOIN condition
  • NULL handling in comparisons (IS NULL vs = NULL)

Key Takeaways

  • CTEs improve readability but aren't always optimized as inline views
  • Window functions eliminate the need for self-joins in many cases