In the previous chapters we discussed on how to fetch data from one table only. In this chapter we are going to see how to get data coming from many tables by using the concept of join. A join between two tables is only possible if an association between each other does exit.
Figure 6.1 : Database schema
1. Simple join
Let’s assume we’d like to get data about all employees who are working in the Marketing department. We’d like to have data about the first name, last name and the department of each employee.
SELECT emp.First_name AS First, emp.Last_name AS Last, dep.Name AS Department
FROM Employee AS emp, Department AS dep
WHERE emp.Department_id = dep.ID AND dep.Name = 'Marketing'
We get the following table.
| {{h}} |
|---|
| {{r}} |
Let’s tell a bit more on how the join does work. In the above example, the join is done within the WHERE clause by the following statement : emp.Department_id = dep.ID. Data of both tables are aggregated (put together) by forming one single table. The join is done using the foreign key Department_id of the Employee table which refers to the primary key of the Department table. Finally a filter is declared as follows : dep.Name = 'Marketing' and thus only the records of the aggregated table satisfying that condition are kept. Figure 6.2 illustrates the above.
Figure 6.2 : SQL join construction
2. Multiple joins
Multiple joins exactly work the same way than the simple join by adding AND in between the different joins. Let’s assume we’d like to get data about all company’s projects as well as the last name and first name of employees involved in each. Three tables are concerned in this case, namely Employee, Project and Involvement.
SELECT proj.Name AS Project, emp.Last_name AS lastName, emp.First_name AS firstName
FROM Employee AS emp, Involvement AS inv, Project AS proj
WHERE inv.Project_id = proj.ID AND inv.Employee_id = emp.ID
We get the following table.
| {{h}} |
|---|
| {{r}} |
Figure 6.3 illustrates how the multiple join above is constructed.
Figure 6.3 : SQL multiple join construction
Recap
A join is needed as far as data are coming from many different tables and then it is necessary to prefix the table’s name before each attribute (e.g. Project.ID). Indeed different tables may have a common attribute name (e.g. ID). In this regard, you may notice that it has be made use of aliases in the examples above for having more convenient table names to deal with.
Another syntax for doing join in SQL does exist. Here is the equivalent way of stating the first example above by using the INNER JOIN syntax.
SELECT emp.First_name AS firstName, emp.Last_name AS lastName, dep.Name AS Department
FROM Employee AS emp
INNER JOIN Department AS dep ON emp.Department_id = dep.ID
WHERE dep.Name = 'Marketing'