CodeRang
SQL topics

SQL JOIN: types, examples, and practical exercises

SQL JOIN combines related rows from multiple tables. Understanding INNER JOIN, LEFT JOIN, and other join types is mainly about predicting which rows will survive the join and whether one match can produce multiple result rows.

Updated · SQL Arena team

SQL from scratch courseAll problems

What you will practice

  • INNER JOIN and join conditions
  • LEFT JOIN and unmatched rows
  • FULL, CROSS, and SELF JOIN
  • anti joins with LEFT JOIN and IS NULL
  • aggregation after one-to-many joins

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Count employees and payroll for every department

You have a departments table and an employees table. Return every department, including departments with no employees. The result must contain its name as department_name, the number of employees as employee_count, and total salaries as payroll. A department with no employees should show 0 for both numeric values.

Query 1PostgreSQLv16
SELECT d.department_name,
       COUNT(e.id) AS employee_count,
       COALESCE(SUM(e.salary), 0) AS payroll
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.id
GROUP BY d.id, d.department_name
ORDER BY d.id;
Submit
Resultdepartmentsemployees
Answer accepted

SQL JOIN example: LEFT JOIN explained step by step

The query starts from departments because every department must appear in the result. That choice matters: a LEFT JOIN preserves rows from its left side. The condition e.department_id = d.id matches each employee to the department referenced by the foreign key. Editorial produces two joined rows, Design produces three, and Archive still produces one row even though no employee matches it; the employee columns in that row are NULL.

This is the central idea behind many SQL join exercises. A one-to-many join expands the data before GROUP BY runs. If three employees belong to Design, the Design row effectively appears three times in the joined set. That is harmless when we deliberately count employees or sum employee salaries, but it can break an aggregate on a parent value. SUM(d.budget), for example, would count Design's budget three times.

COUNT(e.id) is intentional. COUNT(*) would count the preserved Archive row and report 1 even though there is no employee. Counting the child key ignores the NULL created by the unmatched LEFT JOIN and returns 0. SUM(e.salary) returns NULL for Archive, so COALESCE converts it to 0. Finally, grouping by the department and ordering by its id turns the expanded join result back into one deterministic row per department.

The main SQL JOIN types differ in which unmatched rows they preserve. INNER JOIN returns rows that satisfy the join condition on both sides. LEFT JOIN keeps every row from the left table, using NULL for missing matches on the right. RIGHT JOIN does the reverse, while FULL JOIN preserves unmatched rows from both sides. CROSS JOIN creates every possible pair of rows. Before writing a join, decide which table defines the rows that must remain and whether the relationship can produce more than one match per row.

SQLPostgreSQL v16
SELECT d.department_name,
       COUNT(e.id) AS employee_count,
       COALESCE(SUM(e.salary), 0) AS payroll
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.id
GROUP BY d.id, d.department_name
ORDER BY d.id;
Query result
department_nameemployee_countpayroll
Editorial280000
Design3127000
Research2100000
Support136000
Archive00

Common SQL JOIN mistakes

01

Using INNER JOIN when unmatched rows matter

Changing this query to INNER JOIN removes Archive entirely because there is no employee to match. INNER JOIN is correct when only matched pairs belong in the answer; LEFT JOIN is required when objects on the left must remain visible without a related row.

02

Counting rows instead of matches

After a LEFT JOIN, COUNT(*) counts the preserved parent row even when all columns from the child table are NULL. Count a non-null child identifier when the question asks how many matching child records exist.

03

Ignoring one-to-many row multiplication

A join does not magically keep one row per department. Design becomes three rows because three employees match it. Aggregating parent-side values after that expansion can overcount them unless the query is designed around the relationship's cardinality.

Practice problems on this topic

SQL from scratch course

Once this example is clear, useful SQL JOIN practice should vary the reason for joining rather than repeat the same pattern. In SQL Arena, Easy exercises include combining server-error data, counting records by section, ranking cities from completed activity, and calculating unused datacenter capacity. Harder exercises combine joins with richer transformation and aggregation logic. SQL Arena contains 1,192 automatically checked tasks, with different tables for each task. Solutions are validated against hidden data instead of being matched to one expected query. PostgreSQL, MySQL, ClickHouse, and BigQuery each have their own browser-based sandbox, so nothing needs to be installed. The free plan requires no card and includes 292 tasks across all four dialects. If you want a structured path before doing more LEFT JOIN practice problems, continue with the JOIN chapter in the “SQL from Scratch” course, where each lesson pairs explanation with a task.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is an SQL JOIN?

An SQL JOIN combines rows from two or more tables using a relationship between their columns. A common join condition matches a foreign key in one table to a primary key in another.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows with matching records on both sides. LEFT JOIN keeps every row from the left table and fills columns from the right side with NULL when no match exists.

What are the main types of SQL JOIN?

The main join types are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN. SELF JOIN describes joining a table to itself rather than a separate join operator.

Can you show an INNER JOIN example?

A typical example joins orders to customers with a condition such as `orders.customer_id = customers.id`. Only orders with a matching customer row appear when INNER JOIN is used.

Why does an SQL JOIN create duplicate rows?

A join can produce several result rows when one row matches multiple rows in the other table. These are not necessarily accidental duplicates; they can be the correct representation of a one-to-many relationship.

See also

Practice SQL JOIN queries

SQL Arena lets you work through JOIN exercises in PostgreSQL, MySQL, ClickHouse, and BigQuery. The editor includes ER diagrams and EXPLAIN, and submissions are checked on hidden data. An AI mentor can explain errors and provide hints; 10 AI-mentor requests are available for free in total.

Practice SQL JOINs

More practice from CodeRang