SQL Joins Explained With Examples (Inner, Left, Right, Full)

Real databases never keep everything in one table. Customers live in one table and orders in another. To answer “which customers placed orders this month?” you must combine them, and that is what SQL joins explained in this guide covers.

This guide to SQL joins explained keeps everything practical. Joins confuse many beginners because the same two tables give different results depending on the join type. Once you see them with small, concrete data, they become easy.

SQL joins explained: the sample tables

customers

orders

Chitra has no orders, and order 104 points to a customer (9) who does not exist. These two edge cases show the difference between the join types.

INNER JOIN

An INNER JOIN returns only rows where the condition matches in both tables. Rows without a partner are dropped.

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

Result: Asha (101), Asha (102), Ben (103). Chitra and order 104 are missing. Use it when you only care about related data, such as orders with their customer details. Plain JOIN means INNER JOIN.

LEFT JOIN

A LEFT JOIN keeps every row from the left table and adds matching rows from the right. Where there is no match, right-side columns are NULL.

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

Result: Asha (101), Asha (102), Ben (103), Chitra (NULL, NULL). Chitra now appears.

RIGHT JOIN and FULL JOIN

A RIGHT JOIN mirrors a LEFT JOIN and keeps every row from the right table. Most people swap the table order and use LEFT JOIN because it reads more naturally.

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

The result includes order 104 with a NULL customer name. A FULL (OUTER) JOIN returns all rows from both tables: Chitra with no order and order 104 with no customer.

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

PostgreSQL and SQL Server support it. MySQL does not, so combine a LEFT JOIN and a RIGHT JOIN with UNION. For more on SQL joins explained, see the PostgreSQL manual page on table expressions and joins, and its joins tutorial.

CROSS JOIN and SELF JOIN

A CROSS JOIN pairs every row of one table with every row of the other: three customers and four orders give twelve rows. It is rare, but useful for generating combinations such as test data for every size and colour.

SELECT c.name, o.id
FROM customers c
CROSS JOIN orders o;

A SELF JOIN joins a table to itself using aliases, for example to find each employee’s manager.

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

SQL joins explained: common mistakes and performance tips

• Missing ON clause creates a CROSS JOIN by accident and explodes row counts.
• Filtering the right table in WHERE after a LEFT JOIN turns it into an INNER JOIN; put the condition in the ON clause.
• Ambiguous column names: prefix columns with table aliases.
• Joining on non-unique columns multiplies rows and inflates totals.
• Index the columns used in joins, select only the columns you need and check slow queries with EXPLAIN.

Why joins matter for testers

QA engineers use joins to verify that data created through the UI or an API landed correctly in the database, and to find orphan records. Pair this with our API testing with Postman guide. For the basics see the SQL tutorial for beginners, and for newer data stores read about vector databases. To run a database locally, see our Docker tutorial.

Final thoughts on SQL joins explained

SQL joins explained in one line: INNER keeps matches only, LEFT keeps everything on the left, RIGHT keeps everything on the right and FULL keeps all of it. Practise with two tiny tables until you can predict the result before running the query, then try joining three tables and combining joins with GROUP BY.

Frequently asked questions

Keep learning