Joins in SQL: INNER, LEFT, RIGHT, FULL and Self Joins with Examples

Joins in SQL combine rows from two or more tables using a related column, such as a customer ID that appears in both a customers table and an orders table. Almost every real analysis needs a join, because business data is split across tables on purpose. This guide explains each join type with one small dataset, shows the exact output of every query, and covers the join mistakes that come up most often in SQL interview questions.

The sample tables

Every example below uses these two tables. Notice that Kabir has no orders, and order 106 belongs to a customer ID (9) that does not exist in the customers table. Those two rows are what make the join types behave differently.

customers

orders

You can paste these tables and every query on this page into the free Skillancy SQL compiler, which runs real PostgreSQL in your browser.

INNER JOIN: only rows that match

An INNER JOIN returns a row only when the join condition is true on both sides.

SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Kabir disappears because he has no orders, and order 106 disappears because customer 9 does not exist. Writing just JOIN means the same thing as INNER JOIN.

LEFT JOIN: keep everything on the left

A LEFT JOIN keeps every row from the first (left) table. When there is no match, the columns from the right table come back as NULL.

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

LEFT JOIN is the join analysts use most, because reports usually start from a complete list (all customers, all stores, all days) and attach activity to it.

Finding rows with no match (anti-join)

Add a filter on a right-table column being NULL to find left rows with no match at all. This is a classic interview question: "Which customers have never ordered?"

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

RIGHT JOIN: keep everything on the right

A RIGHT JOIN is the mirror image: every row from the second table is kept.

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Order 106 now appears with no customer name. That makes RIGHT JOIN useful for data-quality checks such as "find orders whose customer record is missing". Most teams still prefer to swap the table order and write a LEFT JOIN, because it reads more naturally.

FULL OUTER JOIN: keep everything from both

A FULL OUTER JOIN keeps unmatched rows from both tables.

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id NULLS LAST, o.order_id;

Use it when you reconcile two sources, for example a payments export against an orders table, and need to see mismatches on both sides at once.

CROSS JOIN: every combination

A CROSS JOIN has no join condition. It pairs every row of one table with every row of the other, so 4 customers and 6 orders produce 24 rows.

SELECT COUNT(*) AS row_count
FROM customers c
CROSS JOIN orders o;

It is rarely what you want by accident, but it is useful on purpose, for example to build a grid of every store and every date before filling in sales.

Self join: a table joined to itself

A self join treats one table as two, using aliases. The standard example is an employees table where each row stores its manager's ID.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

It is a LEFT JOIN so that Nisha, who has no manager, is not dropped.

The most common join mistake: filtering a LEFT JOIN in WHERE

This is the join bug interviewers like most. Suppose you want every customer, plus only their orders above ₹700.

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 700
ORDER BY c.customer_id;

Rohan and Kabir vanish. The WHERE clause runs after the join, and NULL > 700 is not true, so the unmatched rows are filtered out and the LEFT JOIN quietly behaves like an INNER JOIN. Move the condition into the ON clause instead:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.amount > 700
ORDER BY c.customer_id;

The rule: conditions on the right table of a LEFT JOIN belong in ON if you want to keep unmatched left rows.

Joins with aggregation

Joins usually feed a GROUP BY. Count with a right-table column, not COUNT(*), so customers with no orders show 0 instead of 1.

SELECT c.name, COUNT(o.order_id) AS orders, COALESCE(SUM(o.amount), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY revenue DESC;

A related trap is joining two one-to-many tables (say orders and payments) to the same customer before aggregating. Each order row multiplies with each payment row and totals are inflated. Aggregate each table first, then join the results; a CTE in SQL makes that easy to read.

Frequently asked questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table and fills in NULL where the right table has no match.

Is JOIN the same as INNER JOIN?

Yes. In standard SQL and PostgreSQL, a plain JOIN is an INNER JOIN.

Which join is used most by data analysts?

LEFT JOIN, because most analysis starts from a complete list of entities and attaches activity to it without losing entities that had none.

How many types of joins are there in SQL?

The main types are INNER, LEFT, RIGHT, FULL OUTER and CROSS. A self join is not a separate keyword; it is any of these joins used between a table and itself.

How do I practice joins in SQL?

Run the examples above in the browser compiler, change the data and predict the output before running each query. Then solve the join problems in our free SQL interview questions set, which checks your answers instantly.

Recommended internal links

• Free online SQL compiler: https://skillancy.in/tools/sql-compiler

• 25 SQL interview questions with a live editor: https://skillancy.in/practice/sql-questions

• CTE in SQL guide: https://skillancy.in/blog/cte-in-sql

• Data Analytics with Agentic AI program: https://skillancy.in/courses/data-analytics-with-agentic-ai

Sources and publishing references

PostgreSQL documentation: Joined tables: https://www.postgresql.org/docs/current/queries-table-expressions.html

All query outputs on this page were produced by running the queries against PostgreSQL.