vizlearn

Modules / Database / Join Operations

SQL Joins Visualizer

Databases & SQLBy Ashish JangraUpdated 26 October 2026

Explore how different SQL JOIN types combine rows from two tables based on a related column.

1. Join Type

Returns records that have matching values in both tables.

2. Join Condition (ON)

Employees.DeptID = Departments.ID

Table A (Left)

EMPLOYEES

ID	Name	DeptID

Table B (Right)

DEPARTMENTS

ID	DeptName


                            SELECT * FROM Employees
                            
INNER JOIN Departments
                            
ON Employees.DeptID = Departments.ID;

Result Set

READY

Emp.ID	Name	Emp.DeptID	Dept.ID	DeptName

Overview

Suppose customers has one row per customer and orders has one row per order. Joining on customer_id returns one row per matching customer-order pair. A customer with three orders appears three times. This is expected for pair-level results; it is not necessarily a duplicate-data problem.

A join makes matching pairs

query.sqlSQLite

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

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

Result

An inner join keeps only rows with matches. A left join keeps every left-side row and fills right-side columns with NULL when no match exists. Right and full joins are available in some engines, but support and syntax vary.

ON and WHERE with an outer join

The ON condition decides which right-side rows match. A WHERE condition runs after the join result is formed. Moving a right-side filter from ON to WHERE can remove the NULL-extended rows and make a left join behave like an inner join.

To keep all customers but attach only paid orders, put the paid condition in ON:

query.sqlSQLite

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid';

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid';

Result

Check cardinality before adding DISTINCT

If a join returns more rows than expected, check whether the join key is unique on the side you thought had one row. Joining two one-to-many relationships can multiply rows and inflate sums. Aggregate each side to the intended grain before joining, or use EXISTS when the question is only whether a match exists. See EXISTS vs JOIN and GROUP BY.

Find unmatched rows

A left anti-join finds rows on the left with no match on the right. One common form is LEFT JOIN followed by WHERE right_table.key IS NULL. Use a right-side column that cannot be NULL for a real matched row; otherwise a match with a NULL value can be mistaken for no match. NOT EXISTS is another clear form. Test the query with one matched row, one unmatched row, and duplicate right-side matches so the output count matches the intended grain.

Aggregate at the intended grain

Suppose an order has several line items and several payments. Joining both child tables directly can create every line-payment combination for that order, multiplying both line totals and payment totals. Aggregate each child table to one row per order before joining, or use separate subqueries for the measures. Check a small order with two lines and three payments: the combined join produces six pairs. This simple count often reveals why a report total doubled or tripled after a new join was added.

Predict, then reveal

About to run: switch Join Type to FULL OUTER JOIN. Before it does — what happens to the readout?

Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.

Check yourself

0 of 3

Answer without scrolling back up.

A LEFT JOIN returns:
You LEFT JOIN, then filter the right table in the WHERE clause. What usually happens?
Joining on a column with duplicate values on both sides produces:

Cheat sheet