SQL JOINs Explained: INNER, LEFT, RIGHT, FULL with Real Examples

SQL JOINs let you combine data from multiple tables — one of the most essential skills in any analyst's toolkit. Whether you're pulling customer orders, linking employees to departments, or building reports — JOINs are at the heart of it all.

In this guide, we'll break down every JOIN type with clear explanations and practical examples using the Chinook database (a digital music store with artists, albums, tracks, invoices, and customers).

Table of Contents

• What Is a JOIN?
• The 4 Types of SQL JOINs
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• Joining More Than Two Tables
• Quick Comparison
• Common Mistakes to Avoid
• Practice Exercises
• FAQ

What Is a JOIN?

A JOIN combines rows from two or more tables based on a related column between them. Think of it as linking rows that share a common value — like matching a customer_id in an orders table to the id in a customers table.

The basic syntax looks like this:

SELECT columns
FROM table_a
JOIN table_b ON table_a.key = table_b.key;

The ON clause defines the relationship — which columns should match.

The 4 Types of SQL JOINs

There are four main JOIN types, each returning a different set of results:

Let's explore each one.

INNER JOIN

Returns only rows that have matching values in both tables.

This is the most common JOIN type. If there's no match, the row is excluded from the result. When comparing SQL LEFT JOIN vs INNER JOIN, the key difference is that INNER JOIN drops unmatched rows entirely.

Example: Albums with Their Artists

SELECT 
  a.Title AS album_title,
  ar.Name AS artist_name
FROM albums a
INNER JOIN artists ar ON a.ArtistId = ar.ArtistId
LIMIT 5;

When to Use INNER JOIN

• You only want rows where the relationship exists in both tables
• Filtering out unmatched data is intentional (e.g., only albums that have a known artist)
• Building reports where incomplete data should be excluded

Try it yourself — Practice INNER JOIN with the Chinook database →

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right table. If there's no match, the right side columns are NULL.

This is the second most commonly used JOIN, and it's critical when you need to keep all records from your primary table even if related data is missing.

Example: All Artists, Even Those Without Albums

SELECT 
  ar.Name AS artist_name,
  COUNT(a.AlbumId) AS album_count
FROM artists ar
LEFT JOIN albums a ON ar.ArtistId = a.ArtistId
GROUP BY ar.Name
HAVING COUNT(a.AlbumId) = 0
LIMIT 5;

This query finds artists who don't have any albums in the database. With an INNER JOIN, these artists would simply disappear from the results.

When to Use LEFT JOIN

• You need all records from the primary (left) table regardless of matches
• Finding orphan records (items without related data)
• Building complete reports where missing data should appear as NULL rather than be excluded

Pro tip: LEFT JOIN and LEFT OUTER JOIN are identical in SQL. The OUTER keyword is optional.

Try it yourself — Practice LEFT JOIN with the Chinook database →

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, plus matching rows from the left table. If there's no match, the left side columns are NULL.

A RIGHT JOIN is the mirror image of a LEFT JOIN. In practice, most SQL developers prefer rewriting a RIGHT JOIN as a LEFT JOIN by swapping the table order — it's easier to read.

Example: All Genres, Including Those Without Tracks

SELECT 
  g.Name AS genre,
  COUNT(t.TrackId) AS track_count
FROM tracks t
RIGHT JOIN genres g ON t.GenreId = g.GenreId
GROUP BY g.Name
ORDER BY track_count ASC
LIMIT 5;

When to Use RIGHT JOIN

• When the table you want to preserve is on the right side of the query
• Most developers prefer LEFT JOIN and simply reverse the table order for readability

Note: SQLite (used in browser-based SQL tools) does not support RIGHT JOIN. You can always rewrite it as a LEFT JOIN by swapping the tables.

Try it yourself — Practice JOIN queries on the Chinook database →

FULL OUTER JOIN

Returns all rows from both tables. Where there's a match, columns are populated from both sides. Where there isn't, the missing side is NULL.

FULL OUTER JOIN is the most inclusive — no data is lost from either table.

Example: Matching Invoices to Customers

Imagine you want to find both customers who haven't made purchases AND invoices that don't have a valid customer:

SELECT 
  c.FirstName || ' ' || c.LastName AS customer_name,
  i.InvoiceId,
  i.Total
FROM customers c
FULL OUTER JOIN invoices i ON c.CustomerId = i.CustomerId
WHERE c.CustomerId IS NULL OR i.InvoiceId IS NULL;

In the Chinook database, every customer has invoices and every invoice has a valid customer — so this query returns no rows. That's actually a sign of good data integrity!

When to Use FULL OUTER JOIN

• Data reconciliation: finding unmatched records on both sides
• Auditing: identifying orphaned records in either table
• Merging datasets from different sources where either side may have missing entries

Note: SQLite does not support FULL OUTER JOIN directly. You can simulate it by combining a LEFT JOIN and a RIGHT JOIN (or two LEFT JOINs) with UNION.

Joining More Than Two Tables

In real-world SQL, you'll frequently need to JOIN multiple tables in a single query. For example, to get invoice details with customer names and track titles, you might chain three or four JOINs together:

SELECT 
  c.FirstName || ' ' || c.LastName AS customer,
  i.InvoiceDate,
  t.Name AS track,
  ii.UnitPrice
FROM invoices i
INNER JOIN customers c ON i.CustomerId = c.CustomerId
INNER JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
INNER JOIN tracks t ON ii.TrackId = t.TrackId
LIMIT 5;

Each JOIN adds another table to the chain. The key is that each ON clause references a column from the newly joined table and a column that's already in the result set.

You can also combine different JOIN types in one query — for example, using an INNER JOIN for required relationships and a LEFT JOIN for optional ones. This is a pattern you'll use daily as a data analyst or backend developer.

Try it yourself — Practice multi-table JOINs with real data →

Quick Comparison

Common Mistakes to Avoid

1. Forgetting the ON clause

Without ON, you get a Cartesian product — every row in table A paired with every row in table B. If both tables have 1,000 rows, that's 1,000,000 results.

2. Using the wrong JOIN type

If you use INNER JOIN when you meant LEFT JOIN, you'll silently lose rows that don't have a match. Always ask: "Do I need to keep unmatched rows?"

3. Not handling NULLs from outer joins

When a LEFT JOIN doesn't find a match, the right-side columns are NULL. Aggregation functions like COUNT(column) skip NULLs, but expressions like price * quantity will return NULL if either value is NULL. Use COALESCE() to handle this:

SELECT 
  ar.Name,
  COALESCE(COUNT(a.AlbumId), 0) AS album_count
FROM artists ar
LEFT JOIN albums a ON ar.ArtistId = a.ArtistId
GROUP BY ar.Name;

4. Joining on the wrong column

Double-check that the columns in your ON clause actually represent the same entity. Joining customers.Id to invoices.Id instead of invoices.CustomerId is a classic bug that produces nonsense results without any error.

5. SQL JOIN with WHERE clause — order matters

When using a LEFT JOIN with a WHERE clause on the right table, you can accidentally turn it into an INNER JOIN:

-- ❌ This filters out NULLs, behaving like INNER JOIN
SELECT * FROM artists ar
LEFT JOIN albums a ON ar.ArtistId = a.ArtistId
WHERE a.Title LIKE '%Rock%';

-- ✅ Move the condition to the ON clause to preserve the LEFT JOIN behavior
SELECT * FROM artists ar
LEFT JOIN albums a ON ar.ArtistId = a.ArtistId AND a.Title LIKE '%Rock%';

Practice Makes Perfect

The best way to internalize JOINs is to write them yourself. Here are exercises you can try right now on SQiLs — no setup required, just write SQL in your browser:

Join 500+ SQL learners already practicing on SQiLs — Browse all exercises →

Summary

SQL JOINs are the backbone of relational database queries. Here's what to remember:

• INNER JOIN = only matches (the strict one)
• LEFT JOIN = keep everything from the left table (the inclusive one)
• RIGHT JOIN = keep everything from the right table (rarely used, just flip your LEFT JOIN)
• FULL OUTER JOIN = keep everything from both tables (the audit one)

Start with INNER and LEFT JOIN — they cover 95% of real-world use cases. Once you're comfortable, you'll know exactly when to reach for the others.

Ready to put this into practice? Start solving JOIN exercises on SQiLs →

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables — if a row in the left table has no corresponding row in the right table, it's excluded. LEFT JOIN keeps all rows from the left table and fills in NULL for the right-side columns when there's no match. Use INNER JOIN when you only want complete data; use LEFT JOIN when you need to see everything from the primary table, including items with missing relationships.

Can you use multiple JOINs in one SQL query?

Yes! You can chain as many JOINs as you need. Each JOIN adds another table to your query. For example, you might JOIN invoices to customers, then JOIN invoice_items to invoices, then JOIN tracks to invoice_items — all in one query. The key is that each ON clause connects the new table to a column already available in the query.

What is a self JOIN in SQL?

A self JOIN is when a table is joined to itself. This is useful for hierarchical data — like an employees table where each employee has a manager_id that references another row in the same table. You use table aliases to distinguish the two copies: FROM employees e JOIN employees m ON e.manager_id = m.id.

Why is my SQL JOIN returning duplicate rows?

Duplicates usually mean the JOIN condition matches one row to multiple rows in the other table. For example, if one customer has 10 invoices, joining customers to invoices will produce 10 rows for that customer. To fix this, use DISTINCT, aggregate with GROUP BY, or verify your JOIN condition is targeting the right columns.