Summary: in this tutorial, we will show you how to use the SQL INNER JOIN clause to query data from two or more tables.
Tóm Tắt
Introduction to the SQL INNER JOIN clause
So far, you have learned how to use the SELECT statement to query data from a single table. However, the SELECT
statement is not limited to query data from a single table. The SELECT
statement can link multiple tables together.
The process of linking tables is called joining. SQL provides many kinds of joins such as inner join, left join, right join, full outer join, etc. This tutorial focuses on the inner join.
Suppose, you have two tables: A and B.
Table A has four rows: (1,2,3,4) and table B has four rows: (3,4,5,6)
When table A joins with table B using the inner join, you have the result set (3,4) that is the intersection of table A and table B.
See the following picture.
For each row in table A, the inner join clause finds the matching rows in table B. If a row is matched, it is included in the final result set.
Suppose the columns in the A and B tables are a
and b
. The following statement illustrates the inner join clause:
SELECT
aFROM
AINNER
JOIN
BON
b = a;Code language:
SQL (Structured Query Language)
(
sql
)
The INNER JOIN clause appears after the FROM clause. The condition to match between table A and table B is specified after the ON keyword. This condition is called join condition i.e., B.n = A.n
The INNER JOIN clause can join three or more tables as long as they have relationships, typically foreign key relationships.
For example, the following statement illustrates how to join 3 tables: A, B, and C:
SELECT
A.nFROM
AINNER
JOIN
BON
B.n = A.nINNER
JOIN
CON
C.n = A.n;Code language:
SQL (Structured Query Language)
(
sql
)
SQL INNER JOIN examples
Let’s take some practical examples of using the INNER JOIN
clause.
1) Using SQL INNER JOIN to join two tables example
We will use the employees
and departments
tables from the sample database to demonstrate how the INNER JOIN
clause works.
Each employee belongs to one and only one department while each department can have more than one employee. The relationship between the departments
and employees
is one-to-many.
The department_id
column in the employees
table is the foreign key column that links the employees
to the departments
table.
To get the information of the department id 1,2, and 3, you use the following statement.
SELECT
department_id, department_nameFROM
departmentsWHERE
department_idIN
(1
,2
,3
);Code language:
SQL (Structured Query Language)
(
sql
)
Try It
Notice that we used the IN operator in the WHERE clause to get rows with department_id 1, 2 and 3.
To get the information of employees who work in the department id 1, 2 and 3, you use the following query:
SELECT
first_name, last_name, department_idFROM
employeesWHERE
department_idIN
(1
,2
,3
)ORDER
BY
department_id;Code language:
SQL (Structured Query Language)
(
sql
)
Try It
To combine data from these two tables, you use an inner join clause as the following query:
SELECT
first_name, last_name, employees.department_id, departments.department_id, department_nameFROM
employeesINNER
JOIN
departmentsON
departments.department_id = employees.department_idWHERE
employees.department_idIN
(1
,2
,3
);Code language:
SQL (Structured Query Language)
(
sql
)
Try It
For each row in the employees
table, the statement checks if the value of the department_id
column equals the value of the department_id
column in the departments
table. If the condition
If the condition employees.department_id = departments.department_id
is satisfied, the combined row that includes data from rows in both employees
and departments
tables are included in the result set.
Notice that both employees
and departments
tables have the same column name department_id
, therefore we had to qualify the department_id
column using the syntax table_name.column_name
.
SQL INNER JOIN 3 tables example
Each employee holds one job while a job may be held by many employees. The relationship between the jobs
table and the employees
table is one-to-many.
The following database diagram illustrates the relationships between employees
, departments
and jobs
tables:
The following query uses the inner join clauses to join 3 tables: employees, departments, and jobs to get the first name, last name, job title, and department name of employees who work in department id 1, 2, and 3.
SELECT
first_name, last_name, job_title, department_nameFROM
employees eINNER
JOIN
departments dON
d.department_id = e.department_idINNER
JOIN
jobs jON
j.job_id = e.job_idWHERE
e.department_idIN
(1
,2
,3
);Code language:
SQL (Structured Query Language)
(
sql
)
Try It
Now you should understand how the SQL INNER JOIN clause works and know how to apply it to query data from multiple tables.
Was this tutorial helpful ?