SQL joins explained with examples: inner, left, right and full

SQL joins combine rows from two tables whenever a condition, usually matching keys, is true. An inner join returns only matching pairs. A left join also keeps unmatched rows of the left table, a right join keeps unmatched rows of the right table, and a full join keeps unmatched rows from both sides, filling the gaps with NULL. A cross join pairs every row with every row.

What do the four main SQL joins return?

The four main SQL joins differ only in what happens to rows that find no partner. PostgreSQL's manual defines inner, outer and cross joins, with LEFT, RIGHT and FULL as the outer variants. Every result below was run on SQLite 3.54.0, which supports all four types, using two small tables from a hypothetical online shop.

The customers table has three rows. Chitra has placed no order.

The orders table has four rows. Order 104 is a guest checkout, so its customer_id is NULL.

The table below summarises what each join keeps on these two tables.

What is the difference between inner join and left join?

An inner join returns only rows that have a match in both tables, while a left join returns every row of the left table and fills the right-hand columns with NULL where nothing matches. PostgreSQL's manual defines an inner join as one row for each pair of rows that satisfies the join condition. JOIN on its own means INNER JOIN.

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

Chitra is missing because she has no order. A left join keeps her, since the result always has at least one row for each row of the left table.

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

Adding WHERE o.order_id IS NULL to the left join keeps only the rows with no match. MySQL's 8.4 manual describes this as a way to find rows in one table that have no counterpart in another. Here it returns one row, Chitra, so the pattern lists customers who never ordered.

Row order in these results is simply what SQLite returned. Without ORDER BY, PostgreSQL's manual says rows come back in whatever order the system finds fastest to produce.

What do right join and full outer join return?

A right join is the mirror image of a left join: it returns every row of the right table and fills the left-hand columns with NULL where nothing matches. The same orders table, joined from the other side, now keeps the guest checkout.

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

MySQL's manual says RIGHT JOIN works analogously to LEFT JOIN and recommends LEFT JOIN to keep code portable across databases. Swapping the table order turns any right join into a left join.

A full outer join is an inner join plus the unmatched rows of both tables, each padded with NULL on the other side. Here that is the three matched rows, Chitra and order 104.

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

Does MySQL support full outer join?

MySQL does not document a FULL JOIN. The MySQL 8.4 manual's JOIN Clause page covers INNER, CROSS, LEFT and RIGHT joins, writes outer joins as LEFT or RIGHT only, and never mentions FULL. PostgreSQL's manual lists FULL as an outer join type, and SQLite added RIGHT and FULL OUTER JOIN in release 3.39.0 on 25 June 2022.

On MySQL, the usual substitute is a left join followed by UNION ALL with the right-side rows that found no match. UNION ALL matters here: PostgreSQL's manual says plain UNION removes duplicate rows, which could delete legitimate repeated rows from a full-join result.

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION ALL
SELECT NULL, o.order_id, o.amount
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

The five rows match the FULL JOIN result above. The query uses only LEFT JOIN, UNION ALL and NOT EXISTS, but it was run on SQLite, not MySQL. The wider question of SQL as a language versus MySQL as a database is covered in an earlier post on SQL vs MySQL.

What are cross join and self join used for?

A cross join returns every combination of rows from two tables, so tables with N and M rows give N * M rows. PostgreSQL's manual says it is equivalent to INNER JOIN ON TRUE and to listing both tables with a comma. The 3-row customers table and 4-row orders table give 12 rows.

SELECT COUNT(*) AS rows_returned FROM customers CROSS JOIN orders;

A self join joins a table to itself, which needs two aliases for the same table. A typical case is a staff table where each row stores its manager's id. In this hypothetical table, Meera has no manager, so a left join keeps her row.

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

Why does a join return duplicate rows?

A join returns one row per matching pair, so a customer with two orders appears twice. Nothing is wrong with the query: the one-to-many relationship repeats the customer row, and any customer-level column repeats with it.

SELECT COUNT(*) AS rows_after_join,
       COUNT(DISTINCT c.customer_id) AS customers,
       SUM(c.credit_limit) AS total_credit_limit
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Asha's credit limit of 5000 was counted twice, so the total reads 14000 when the true figure for the two customers who ordered is 9000. The fix is to aggregate the many side first, in a subquery, and join the result, so each customer appears once.

SELECT c.name, c.credit_limit, t.order_total
FROM customers c
JOIN (SELECT customer_id, SUM(amount) AS order_total
      FROM orders GROUP BY customer_id) t
  ON t.customer_id = c.customer_id;

COUNT(DISTINCT ...) and EXISTS are other ways to count each customer once. Check the row count after every join against the number of rows expected.

Should a condition go in ON or in WHERE?

For an inner join the two places give the same rows, but for an outer join they do not. PostgreSQL's manual says a restriction in ON is processed before the join and one in WHERE after it, and that this matters a lot for outer joins. Take the condition o.amount > 2000 on the customers-to-orders left join.

-- condition in ON
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 2000;

-- condition in WHERE
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 2000;

The ON version returns 3 rows and keeps Chitra. The WHERE version returns 2 rows, because the NULL in Chitra's amount fails the comparison and the row is removed after the join. A WHERE condition on the optional side quietly turns a left join into an inner join. Put conditions about the optional table in ON when the unmatched left rows must survive.

Which join mistakes and limits catch beginners?

Most join errors come from keys, defaults and assumptions that look harmless in a small example. These are the ones that recur.

• NULL keys never match. PostgreSQL's manual says comparison operators yield null, not true, when either input is null, so order 104 above can never match a customer in an ON condition.
• NATURAL JOIN joins on every column name the two tables share. PostgreSQL's manual calls it considerably more risky than USING, because adding a column with a matching name later changes the join.
• In MySQL, JOIN, CROSS JOIN and INNER JOIN are syntactic equivalents, so a join written without an ON condition returns the Cartesian product. Standard SQL uses INNER JOIN with ON and CROSS JOIN otherwise.
• Unqualified column names break when a second table gains a column of the same name, so qualify every column with its table alias.

These examples also have limits. They are tiny, they ran on SQLite rather than PostgreSQL or MySQL, and they say nothing about speed, which depends on indexes and the database's query planner. Treat the result tables as a model of the logic, then test the same query on the target database.

How do SQL joins fit into a data analytics programme?

Analyst questions usually need data from more than one table, such as customers, orders and products, so joins appear in almost every SQL task. The Certification Program in Data Analytics with AI and Gen AI is an online programme from E&ICT Academy, IIT Roorkee, run with Masai as the implementation partner, with live sessions and an optional campus immersion. It covers SQL, Python, Google Sheets, prompt engineering, LLM tools and agentic workflows with LangChain and CrewAI, and a capstone project, and its listed topics include relational databases and SQL queries.

Frequently asked questions

What is the difference between inner join and left join?

An inner join returns only rows with a match in both tables. A left join returns every row from the left table and fills the right-hand columns with NULL when nothing matches. On the sample tables above, the inner join returns 3 rows and the left join returns 4, the extra row being Chitra, who has no order.

Does MySQL support full outer join?

The MySQL 8.4 manual documents LEFT and RIGHT outer joins and does not list a FULL JOIN. The usual substitute combines a LEFT JOIN with a second query that selects the unmatched right-side rows, joined by UNION ALL. PostgreSQL's manual lists FULL as an outer join type, so the keyword works there directly.

Why does a join return duplicate rows?

A join returns one row for every matching pair of rows. If one customer has two orders, the customer appears twice, along with every customer-level column. The result is correct for the query written. To count or sum customer-level values, aggregate the orders first in a subquery, or count distinct customers.

What is the difference between JOIN and INNER JOIN?

In PostgreSQL, INNER is the default join type, so JOIN and INNER JOIN mean the same thing. The MySQL 8.4 manual goes further and says JOIN, CROSS JOIN and INNER JOIN are syntactic equivalents there. Writing INNER JOIN with an ON condition states the intent more clearly.

Can SQL join more than two tables?

Yes. PostgreSQL's manual says joins of all types can be chained or nested, and that without parentheses JOIN clauses are applied from left to right. Each added JOIN takes the result of the previous join as its left input, so the row count can grow or shrink at every step. Check it after each one.

What happens when a join column contains NULL?

Rows with a NULL join key never match, because comparing anything with NULL yields null rather than true. In the sample data, order 104 has a NULL customer_id, so an inner join drops it, while a right or full join keeps it with NULL in the customer columns.

Next step

Rerun every query above on a small sample database and change the data to see which rows appear and disappear. For a structured path through SQL, Python and Google Sheets, see the Certification Program in Data Analytics with AI and Gen AI. Two follow-on skills are SQL window functions, which add rankings and running totals on top of joined data, and data cleaning techniques for fixing the keys and duplicates that break joins.