SQL Joins Introduction — How to Combine Data from Multiple Tables

Why Do We Need JOINs?

Imagine a company database with two tables: Employees stores employee information, and Departments stores department details. Instead of repeating department names in every employee row, we store a DeptID that links to the Departments table. This is called a foreign key relationship.

+----+-----------+--------+--------+
| ID | Name      | DeptID | Salary |
+----+-----------+--------+--------+
| 1  | Priya K.  | 101    | 55000  |
| 2  | John D.   | 102    | 62000  |
| 3  | Arun M.   | 101    | 78000  |
| 4  | Emma W.   | 103    | 51000  |
| 5  | Ravi S.   | NULL   | 72000  |
+----+-----------+--------+--------+

+-----+----------------+-----------+
| ID  | DeptName       | Location  |
+-----+----------------+-----------+
| 101 | IT             | Delhi     |
| 102 | Marketing      | London    |
| 103 | Sales          | Mumbai    |
| 104 | HR             | New York  |
+-----+----------------+-----------+

Notice that Employees.DeptID matches Departments.ID. This is the link between the two tables. JOINs use this link to combine the data.

The Four Types of SQL JOINs

INNER JOIN — Only Matching Rows

INNER JOIN returns only the rows where there is a match in both tables:

SELECT e.Name, d.DeptName, e.Salary
FROM Employees AS e
INNER JOIN Departments AS d
  ON e.DeptID = d.ID;

+-----------+-----------+--------+
| Name      | DeptName  | Salary |
+-----------+-----------+--------+
| Priya K.  | IT        | 55000  |
| John D.   | Marketing | 62000  |
| Arun M.   | IT        | 78000  |
| Emma W.   | Sales     | 51000  |
+-----------+-----------+--------+

Ravi S. is excluded because his DeptID is NULL (no matching department). HR is excluded because no employee belongs to department 104.

How the JOIN Works Step by Step

The database processes this query in these steps:

1. FROM — Start with the Employees table (left table)
2. JOIN — Pair it with the Departments table (right table)
3. ON — Match rows where e.DeptID = d.ID
4. SELECT — Return the requested columns from matched rows

LEFT JOIN — All Left Rows + Matches

LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, the right columns are filled with NULL:

SELECT e.Name, d.DeptName, e.Salary
FROM Employees AS e
LEFT JOIN Departments AS d
  ON e.DeptID = d.ID;

+-----------+-----------+--------+
| Name      | DeptName  | Salary |
+-----------+-----------+--------+
| Priya K.  | IT        | 55000  |
| John D.   | Marketing | 62000  |
| Arun M.   | IT        | 78000  |
| Emma W.   | Sales     | 51000  |
| Ravi S.   | NULL      | 72000  |
+-----------+-----------+--------+

Ravi S. now appears with NULL for DeptName because LEFT JOIN keeps all employees regardless of whether they have a department.

RIGHT JOIN — All Right Rows + Matches

RIGHT JOIN returns all rows from the right table and matching rows from the left:

SELECT e.Name, d.DeptName, d.Location
FROM Employees AS e
RIGHT JOIN Departments AS d
  ON e.DeptID = d.ID;

+-----------+-----------+-----------+
| Name      | DeptName  | Location  |
+-----------+-----------+-----------+
| Priya K.  | IT        | Delhi     |
| Arun M.   | IT        | Delhi     |
| John D.   | Marketing | London    |
| Emma W.   | Sales     | Mumbai    |
| NULL      | HR        | New York  |
+-----------+-----------+-----------+

The HR department appears with NULL for Name because no employee is assigned to it.

FULL OUTER JOIN — All Rows from Both Tables

FULL OUTER JOIN returns all rows from both tables, with NULL where there is no match:

SELECT e.Name, d.DeptName
FROM Employees AS e
FULL OUTER JOIN Departments AS d
  ON e.DeptID = d.ID;

+-----------+-----------+
| Name      | DeptName  |
+-----------+-----------+
| Priya K.  | IT        |
| John D.   | Marketing |
| Arun M.   | IT        |
| Emma W.   | Sales     |
| Ravi S.   | NULL      |
| NULL      | HR        |
+-----------+-----------+

Both Ravi (no department) and HR (no employees) appear in the output.

JOIN Comparison Summary

For a complete reference of JOIN syntax, see the official MySQL JOIN documentation. In the next chapter, you will practice SQL INNER JOIN in depth with more examples.

If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.

📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.

SELECT e.Name,
       d.DeptName,
       d.Location,
       e.Salary
FROM Employees AS e
LEFT JOIN Departments AS d
  ON e.DeptID = d.ID;

+-----------+-----------+-----------+--------+
| Name      | DeptName  | Location  | Salary |
+-----------+-----------+-----------+--------+
| Priya K.  | IT        | Delhi     | 55000  |
| John D.   | Marketing | London    | 62000  |
| Arun M.   | IT        | Delhi     | 78000  |
| Emma W.   | Sales     | Mumbai    | 51000  |
| Ravi S.   | NULL      | NULL      | 72000  |
+-----------+-----------+-----------+--------+

📖 Chapter 20 of 30 — SQL Tutorial on Neotech Navigators