SQL Joins Explicados: Guia Visual com Exemplos

SQL joins are the backbone of relational databases. They let you combine data from two or more tables based on related columns. Despite being fundamental, joins remain one of the most confusing topics for developers. This guide uses visual diagrams and practical examples to make every join type crystal clear.

Sample Data: Our Two Tables

Throughout this guide, we will use these two simple tables:

-- employees table
+----+----------+---------------+
| id | name     | department_id |
+----+----------+---------------+
| 1  | Alice    | 1             |
| 2  | Bob      | 2             |
| 3  | Charlie  | 1             |
| 4  | Diana    | NULL          |
+----+----------+---------------+

-- departments table
+----+-------------+
| id | dept_name   |
+----+-------------+
| 1  | Engineering |
| 2  | Marketing   |
| 3  | Sales       |
+----+-------------+

INNER JOIN

Returns only the rows that have matching values in both tables. Think of it as the intersection of two sets.

    Table A         Table B
  +---------+     +---------+
  |         |     |         |
  |    +----+-----+----+    |
  |    |  INNER JOIN   |    |
  |    +----+-----+----+    |
  |         |     |         |
  +---------+     +---------+

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Result:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
+----------+-------------+
-- Diana excluded (department_id is NULL)
-- Sales excluded (no employee in Sales)

Use when you only want records that exist in both tables.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. If there is no match, NULL values are returned for right table columns.

    Table A         Table B
  +---------+     +---------+
  |XXXXXXXXX|     |         |
  |XXXX+----+-----+----+    |
  |XXXX| LEFT JOIN XXXX|    |
  |XXXX+----+-----+----+    |
  |XXXXXXXXX|     |         |
  +---------+     +---------+
  (all of A + matching B)

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Result:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
| Diana    | NULL        |
+----------+-------------+
-- Diana included with NULL dept_name

Use when you need all records from the left table, regardless of whether they have matches.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and matching rows from the left table. If there is no match, NULL values are returned for left table columns.

    Table A         Table B
  +---------+     +---------+
  |         |     |XXXXXXXXX|
  |    +----+-----+XXXX+XXXX|
  |    |XXXX RIGHT JOINXXXX|
  |    +----+-----+XXXX+XXXX|
  |         |     |XXXXXXXXX|
  +---------+     +---------+
  (matching A + all of B)

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Result:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Charlie  | Engineering |
| Bob      | Marketing   |
| NULL     | Sales       |
+----------+-------------+
-- Sales included with NULL name

Use when you need all records from the right table, regardless of whether they have matches.

FULL OUTER JOIN

Returns all rows from both tables. Where there is no match, NULL values fill in the missing side.

    Table A         Table B
  +---------+     +---------+
  |XXXXXXXXX|     |XXXXXXXXX|
  |XXXX+----+-----+XXXX+XXXX|
  |XXXX|FULL OUTER JOINXXXX|
  |XXXX+----+-----+XXXX+XXXX|
  |XXXXXXXXX|     |XXXXXXXXX|
  +---------+     +---------+
  (everything from both)

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Result:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
| Diana    | NULL        |
| NULL     | Sales       |
+----------+-------------+

Use when you need all records from both tables, including unmatched rows on either side.

CROSS JOIN

Returns the Cartesian product of both tables — every row from the left table is combined with every row from the right table. No ON clause is needed.

SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- Returns 4 × 3 = 12 rows

Result:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Alice    | Marketing   |
| Alice    | Sales       |
| Bob      | Engineering |
| Bob      | Marketing   |
| Bob      | Sales       |
| Charlie  | Engineering |
| Charlie  | Marketing   |
| Charlie  | Sales       |
| Diana    | Engineering |
| Diana    | Marketing   |
| Diana    | Sales       |
+----------+-------------+

Use for generating combinations, test data, or calendar grids. Avoid on large tables.

Self JOIN

A table joined with itself. Useful for hierarchical data or comparing rows within the same table.

-- employees_v2 with manager_id column
+----+---------+------------+
| id | name    | manager_id |
+----+---------+------------+
| 1  | Alice   | NULL       |
| 2  | Bob     | 1          |
| 3  | Charlie | 1          |
| 4  | Diana   | 2          |
+----+---------+------------+

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

Result:

+----------+---------+
| employee | manager |
+----------+---------+
| Alice    | NULL    |
| Bob      | Alice   |
| Charlie  | Alice   |
| Diana    | Bob     |
+----------+---------+

Use for employee-manager relationships, hierarchical categories, or row comparisons.

Which Join to Use? Quick Reference

Performance Tips

• Always index your JOIN columns — this is the single biggest performance improvement.
• Avoid SELECT * in production queries. Select only the columns you need.
• Put the smaller table first in the FROM clause when possible (though most optimizers handle this).
• Use EXPLAIN ANALYZE to inspect your query execution plan.
• Prefer EXISTS over IN for correlated subqueries — it short-circuits on the first match.
• Avoid joining on expressions or functions (e.g., JOIN ON LOWER(a.name) = LOWER(b.name)) — indexes cannot be used.

-- Create index on JOIN column
CREATE INDEX idx_emp_dept ON employees(department_id);

-- Use EXPLAIN to check query plan
EXPLAIN ANALYZE
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Common Mistakes

-- ❌ Wrong: WHERE turns LEFT JOIN into INNER JOIN
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.dept_name = 'Engineering';   -- filters out NULLs!

-- ✅ Correct: Put condition in ON clause
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id
  AND d.dept_name = 'Engineering';   -- preserves NULLs

Frequently Asked Questions