SQL JOINS

A JOIN clause combines rows from two or more tables based on a column filed between them (the foreign key).

The JOIN clause creates a set of rows in a temporary table to combine rows from different tables.

Often a JOIN clause is faster than a subquery. So it is preferred to use the JOIN clause when it is possible.

Demo Table

To understand the JOIN clause, let us see the following "Employee" and "Department" tables.

• Table: Employee:

  +--------+-------------+-----------+-----------+---------+
  | emp_no | first_name  | last_name | salary    | dept_id |
  +--------+-------------+-----------+-----------+---------+
  | 1001   | James       | Smith     | 6600      | 1       |
  | 1002   | Maria       | Martinez  | 9000      | 2       |
  | 1003   | Andrew      | Rodriguez | 9500      | 3       |
  | 1004   | Robert      | Hernandez | 9600      | 2       |
  | 1005   | Donna       | Williams  | 7400      | NULL    |
  | 1006   | James       | Johnson   | 8000      | NULL    |
  | 1007   | Stephanie   | Miller    | 8500      | 8       |
  | 1008   | Nelson      | Lopez     | 7300      | 8       |
  +--------+-------------+-----------+-----------+---------+
  

• Table : Department:

  +---------+------------------+
  | dept_id | dept_name        |
  +---------+------------------+
  | 1       | Customer Service |
  | 2       | Development      |
  | 3       | Finance          |
  | 4       | Human Resources  |
  | 5       | Marketing        |
  | 6       | Production       |
  | 7       | Research         |
  | 8       | Sales            |
  +---------+------------------+
  

SQL JOIN Example

As you can see in the "Demo Table" section, the "dept_id" column of the "Employee" table is the foreign key to the "Departement" table. These two tables are good candidates to use the JOIN clause to combine data between them.

The following SQL statement containing an INNER JOIN will select all rows with matching values in both tables.

SELECT employee.emp_no, employee.first_name, employee.last_name, departement.dept_name
FROM employee
INNER JOIN departement ON employee.dept_id = departement.dep_id;

After the execution, the output will look as follows:

+--------+-------------+-----------+------------------+
| emp_no | first_name  | last_name | dept_name        |
+--------+-------------+-----------+------------------+
| 1001   | James       | Smith     | Customer Service |
| 1002   | Maria       | Martinez  | Development      |
| 1003   | Andrew      | Rodriguez | Finance          |
| 1004   | Robert      | Hernandez | Development      |
| 1007   | Stephanie   | Miller    | Sales            |
| 1008   | Nelson      | Lopez     | Sales            |
+--------+-------------+-----------+------------------+

Note: To join tables, data of the columns that are used for joining tables should match, and not necessarily the names of the column.

Different Types of SQL JOINs

When joining tables, the type of join used in the query control the records that appear in the result set. There are 4 different types of SQL joins:

INNER JOIN

It is the most common type of SQL join. The INNER JOIN only returns rows that have a match in both joined tables.

LEFT OUTER JOIN

The LEFT OUTER JOIN returns all rows from the left-hand table specified in the ON condition and only the matched rows from the right table.

RIGHT OUTER JOIN

The RIGHT OUTER JOIN returns all rows from the right-hand table specified in the ON condition and only the matched rows from the left table.

FULL OUTER JOIN

The FULL OUTER JOIN returns rows from the left and right table with NULL used where the join condition is not met.