Up until now, all the queries you've seen focused on a single table. However, in real-world scenarios, you often need to query multiple tables simultaneously to get a combined result set. This process is called a join, which involves linking different tables based on a common field (the foreign key) to create new views of the data.
To make this easier to understand, let's consider the employees and departments tables. In this example, the dept_id column of the employees table serves as the foreign key to the departments table. This allows these two tables to be joined to retrieve 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 | +---------+------------------+ |
|
Table: employees |
Table: departments |
Note: To join tables, the data in the columns used for joining must match, though the column names don't have to be the same.
When you join tables, the kind of join in your query impacts the rows shown in the result set. You can create these types of joins:
An inner join returns rows that have matches in both tables being joined. For instance, you can join the employees and departments tables to get a result set showing the department name for each employee. With an inner join, employees without department information are excluded, as are departments with no employees.
We will explore more about inner join in the next chapter.
Outer joins expand on inner joins. An outer join includes rows even if they don't have corresponding rows in the joined table. There are three types of outer joins: left outer join (or left join), right outer join (or right join), and full outer join (or full join).
We will discuss these outer join variations in later chapters.
Cross joins are performed without a join condition. Each row from one table is paired with each row from another table, resulting in a Cartesian product or cross product. For example, a cross join between the employees and departments tables produces a result set with a row for every possible employee/department pairing.
We will cover more about cross join in upcoming chapters.