SQL Table Join (Join)

In this tutorial, you will learn how to join two tables to obtain combined data.

SQL Join Basics

So far, all the queries you have seen have been focused on a single table. But in real life, you often need to query two or more tables at once and bring a merged result set. This is technically called a join because it involves joining tables based on common fields (Foreign Key) Joining different tables to create a new view of data.

To understand this point more easily, let's look at the followingemployeesanddepartmentsTable. Here, the dept id column in the employees table is a foreign key in the departments table. Therefore, these two tables can be joined to get combined data.

+--------+--------------+------------+---------+
|　emp_id　|　emp_name　　　　　|　hire_date　　|　dept_id　|
+--------+--------------+------------+---------+
|　　　　　　1　|　Ethan Hunt　　　|　2001-05-01　|　　　　　　　4　|
|　　　　　　2　|　Tony Montana　|　2002-07-15　|　　　　　　　1　|
|　　　　　　3　|　Sarah Connor　|　2005-10-18　|　　　　　　　5　|
|　　　　　　4　|　Rick Deckard　|　2007-01-03　|　　　　　　　3　|
|　　　　　　5　|　Martin Blank　|　2008-06-24　|　　　　NULL　|
+--------+--------------+------------+---------+

+---------+------------------+
|　dept_id　|　dept_name　　　　　　　　|
+---------+------------------+
|　　　　　　　1　|　Administration　　　|
|　　　　　　　2　|　Customer Service　|
|　　　　　　　3　|　Finance　　　　　　　　　　|
|　　　　　　　4　|　Human Resources　　|
|　　　　　　　5　|　Sales　　　　　　　　　　　　|
+---------+------------------+

Join Type

When joining tables, the type of join created in the query affects the rows displayed in the result set. You can create the following types of joins:

Inner Join (Inner join)

The join only returns rows that have matching items in both join tables. For example, you can join the employees anddepartmentsTables are linked together to create a result set that displays the department name for each employee. In an inner join, employees without department information are not included in the result set, and departments without employees will also not be included in the result set.

In the next chapter, we will learn aboutInternally linkedFor more information.

Outer join (Outer join)

Outer join is an extension of inner join. Even if there are no related rows in the join table, outer join will return these rows. There are three types of outer join: left join (left join) right join (right join) and full join (full join)

We will learn in detail about these variants of outer joins in the following chapters.

Cross join (Cross join)

A cross join is a join without a join condition. Each row of one table is merged with each row of the other table. This type of result set is called a Cartesian product or cross product. For example,employeesanddepartmentsThe cross join between tables produces a result set where each possible employee/There is a row for each department combination.

In the following chapters, we will learn aboutCartesian joinFor more information.