SQL INNER JOIN

The INNER JOIN is the most common and frequently used type of joins. It is also called the EQUIJOIN.

The INNER JOIN returns only those rows that have a match in both joined tables.

The following illustration shows how inner join works:

Syntax

The syntax for the INNER JOIN is as follows:

SELECT column_list
FROM tableA
INNER JOIN tableB
ON tableA.column_name = tableB.column_name;

Demo Table

Let us suppose that we have 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 INNER JOIN Example

Now, suppose that you need to select the "emp_no", "first_name", "last_name", and the "dept_name" of only those employees who have been assigned to a department. As we can see in the "Demo Table" section, the employee "Donna Williams" and " James Johnson" are not yet affected to a department, so we don't want to select them.

To make a join, we need a column that has the same data in each tabes. As we can see in our example, the "dept_id" column of the "Employee" table is the foreign key to the "Department" table, and it will be suitable for the joining table.

The following SQL statement will select the employees "emp_no", "first_name", "last_name", and the "dept_name" by joining the "Employee" and "Department" tables using the common column "dept_id". The INNER JOIN will exclude those employees who are not assigned yet to any department.

SELECT em.emp_no, em.first_name, em.last_name, dep.dept_name
FROM employee AS em
INNER JOIN department AS dep
ON em.dept_id = dep.dept_id;

Note:

• If you want to join tables, prefix each column with the name of the table it refers, in order to avoid confusion and column error in the case of columns in different tables that have the same name
• In place of typing the long table names, you can use the table aliases

After running the above SQL statement, the output will look like the following:

+--------+-------------+-----------+------------------+
| 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            |
+--------+-------------+-----------+------------------+

As you can see, the output contains only those employees that have been affected to a department.