QuickDevGuide

Home › PostgreSQL Database

JOINs & Subqueries — PostgreSQL Database

JOINs combine rows from two or more tables based on related columns. They are fundamental to relational databases. JOIN Types Join Type Description INNER JOIN Returns only matching rows from both tables LEFT JOIN All rows from left table + matching from right RIGHT JOIN All rows from right table + matching from left FULL OUTER JOIN All rows from both tables, with NULLs for non-matches CROSS JOIN Cartesian product of both tables SELF JOIN Join a table with itself LATERAL JOIN Subquery can reference columns from preceding tables Subqueries Subqueries (nested queries) can appear in WHERE, FROM, SELECT, or HAVING clauses: Scalar subquery — returns a single value Row subquery — returns a single row Table subquery — returns a result set Correlated subquery — references outer query EXISTS, IN…

Key Takeaways