SQL & Databases Masterclass Course
Welcome to the ultimate postgresql tutorial for beginners 2026. Work through interactive sql practice problems with solutions, master our comprehensive sql window functions tutorial modules, and prepare for tough sql interview questions at Google and Amazon entirely in your browser. Claim your course completion XP and build production-ready database mastery today!
SQL Lesson 7: OUTER JOINs
Introduction & Core Concept
While INNER JOIN discards rows that lack a matching pair in the joined table, business questions often require examining missing data. For example: "Which customers have *never* placed an order?" or "Which artists have *zero* songs in our streaming catalog?"
To answer these questions, we use an OUTER JOIN. Outer joins preserve records from a base table even if no matching key exists in the connecting table. Unmatched slots are automatically filled with NULL placeholders.
LEFT JOIN (or LEFT OUTER JOIN): Retains every single row from the primary (left) table, attaching matching data from the right table.RIGHT JOIN: Retains every single row from the secondary (right) table.FULL JOIN: Retains all rows from both tables, filling non-matches on either side with NULL.Why & Where We Use It
Real-World Example
SpotifyLite wants to audit their music catalog to identify artists who have registered accounts but haven't uploaded any tracks yet. By performing a LEFT JOIN from artists to songs, artists without songs remain in the output, with their song title columns displaying NULL.
Best Practices: What to Do & What NOT to Do
LEFT JOIN across your team rather than mixing LEFT and RIGHT joins. Keeping the primary driving table on the left makes complex multi-join queries incredibly intuitive to read from top to bottom.WHERE filtering conditions on the right-hand table of a LEFT JOIN. Doing so can accidentally convert your left join back into an inner join by filtering out the NULL rows!Syntax & Pro Tips
SELECT a.name AS artist_name, s.title AS song_title
FROM artists a
LEFT JOIN songs s ON a.id = s.artist_id;