SQL JOINs Explained: Inner, Left, Right, and Full

JOINs are fundamental to relational databases. They allow you to combine rows from two or more tables based on related columns, enabling you to retrieve comprehensive data spread across multiple tables.

INNER JOIN returns only rows that have matching values in both tables. Think of it as the intersection of two sets. If an employee has no department, or a department has no employees, those rows won't appear.

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table. Non-matching rows from the right table get NULL values. This is useful when you want all employees regardless of department assignment.

RIGHT JOIN is the opposite — all rows from the right table with matching rows from the left. FULL OUTER JOIN returns all rows from both tables, with NULLs where there's no match.

Practice these concepts in CompileZ's SQL editor. Create two related tables and experiment with each JOIN type to see the results.