Join Types
The join constructs themselves — inner, outer, cross, self, USING/NATURAL, and LATERAL — and exactly which rows each one keeps, drops, or NULL-extends. Interviewers ask you to predict result-set contents and row counts for each construct, often on tiny sample tables.
part ofSQLoverview, primer and where to startread it →on this pageshowhide
explore
- INNER JOIN5 questions
- LEFT and RIGHT OUTER JOIN4 questions
- FULL OUTER JOIN4 questions
- CROSS JOIN5 questions
- Self-Joins5 questions
- USING and NATURAL Joins5 questions
- LATERAL Joins5 questions
- AI & Data Scientistrole
- AI Engineerrole
- BI Analystrole
- Backend Developerrole
- Cyber Security Expertrole
- Data Analystrole
- Data Engineerrole
- Full Stack Developerrole
- Java Backend Developerrole
- Java SDETrole
- Kotlin Backend Developerrole
- MLOps Engineerrole
- Machine Learning Engineerrole
- PostgreSQL DBArole
- QA Engineerrole
- SQLskill
questions
page 2 of 2Why would you CROSS JOIN a table to a one-row subquery such as (SELECT SUM(amount) AS total FROM sales)?
basics
~20 sPairing N rows with a one-row subquery yields N rows, attaching that subquery's value — a grand total, say — to every row, so each row can be compared against it without repeating the aggregate query in the select list.
With FULL OUTER JOIN ... USING (product_id), what does the single product_id column hold for unmatched rows?
basics
~20 sThe merged column is the coalesce of both sides, so a row present on only one side shows that side's product_id rather than NULL. With an ON join you would have to write COALESCE(a.product_id, b.product_id) yourself.
When would you standardise on LATERAL for per-row lookups across a multi-engine codebase?
basics
~20 sStandardise on it when every target engine supports it or its APPLY spelling and the queries genuinely need per-row subqueries returning several rows or columns. Otherwise prefer formulations that are portable everywhere and keep the LATERAL variants isolated behind engine-specific code.
showing 31–33 of 33