intermediateCore SQL100% Free

SQL JOINs Explained

Definition

SQL JOINs combine rows from two or more tables based on a related column, enabling queries that span multiple tables in a relational database.

Introduction to SQL JOINs Explained

JOINs are the backbone of relational databases. They allow you to retrieve data spread across multiple tables by matching rows on a common key. Understanding the six types of JOINs — INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF — is essential for any data professional.

Syntax

SQL
SELECT t1.col, t2.col
FROM table1 t1
[INNER|LEFT|RIGHT|FULL OUTER] JOIN table2 t2
  ON t1.key = t2.key
WHERE condition;

Examples

1

INNER JOIN — Only Matching Rows

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Returns only employees who have a matching department. Employees without a department are excluded.

Try in Playground
2

LEFT JOIN — All Left Rows

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Returns ALL employees, including those with no department (NULL for department columns).

Try in Playground
3

Multiple JOINs

SELECT e.name, d.name AS dept, p.name AS project
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
INNER JOIN projects p ON e.id = p.lead_id;

Chains two JOINs to retrieve employee name, their department, and their project simultaneously.

Try in Playground

Common Mistakes

Forgetting the ON clause — produces a CROSS JOIN (every row × every row = massive result)

Using the wrong join type — INNER JOIN when you need LEFT JOIN loses unmatched rows

Not using aliases — e.name vs d.name avoids ambiguous column reference errors

Joining on non-indexed columns — always index your foreign keys for join performance

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows where there is a match in BOTH tables. LEFT JOIN returns ALL rows from the left table, and matching rows from the right table (NULL for right-side columns when there is no match).
What is a CROSS JOIN in SQL?
A CROSS JOIN returns the Cartesian product of two tables — every row from the first table combined with every row from the second table. If table A has 100 rows and table B has 50 rows, CROSS JOIN produces 5,000 rows.
What is a SELF JOIN?
A SELF JOIN joins a table with itself, using two different aliases. Used for hierarchical data like employee-manager relationships: SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id;
Which SQL JOIN is the most common?
INNER JOIN is the most commonly used. LEFT JOIN is the second most common, especially when you need to preserve all records from one table regardless of whether there is a match in the other table.

Related SQL Topics

Where ClauseGroup BySubqueriesWindow FunctionsIndexing

Practice This in the SQL Playground

Write real queries, see live results, and master SQL JOINs Explained hands-on. 100% free.

Open SQL PlaygroundBrowse All Lessons