What are SQL Multiple JOINS?
FREE
Multiple joins in SQL are used to join records of more than two tables based on related columns between them. We can perform the same or different types of joins on different tables as per the requirement. Multiple matrix multiplications can be related to SQL multiple join except for the fact that multiple join is much more flexible, we can join selected rows and columns as well.
Whenever we perform more than one join of the same or different type on multiple tables in a single query, it is called Multiple Join in SQL.
Types of SQL JOINS
- INNER JOIN - It returns rows/records that have matching values in both tables.
- LEFT (OUTER) JOIN - It returns all the rows that are present in the left table and only matching rows from the right table.
- RIGHT (OUTER) JOIN - It returns all the rows that are present in the right table and only matching rows from the left table.
- FULL (OUTER) JOIN - It returns all the rows from both the left and right tables.
Syntax
Let us now see how we can combine more than two tables using Multiple joins in SQL. Here is a syntax for 3 tables, the same can be extended for 4 or more tables.
In the above syntax, we are performing inner join on table1 and table2 and left join on the resultant table from inner join and table3. We can also specify conditions to select only specific records.
Examples of SQL Multiple JOINS
Let us take an example of the Company's database. It has many relations out of which only 3 are of our interest. These are EMP, PROJECT, AND EMP_PROJ. EMP table has all the data related to the employee like their ID, NAME, JOB, HIRE DATE, etc., the PROJECT table only has the name of the project associated with the project ID, and the EMP_PROJ gives the data of employees who completed particular projects, time is taken by them, and so on.
Now, we are required to fetch the data of employees' names and project names from these tables. None of the tables independently contains the information so we will be performing multiple joins to fetch the data. Also, let us say we want the names of only those employees who worked for more than or equal to 1.00 EMPTIME.
EMP TABLE:
| EMPNO | FIRSTNAME | LASTNAME | HIREDATE | JOB |
|---|---|---|---|---|
| 000010 | CHRISTINE | HAAS | 1965-01-01 | PRES |
| 000030 | SALLY | KWAN | 1975-04-05 | MANAGER |
| 000100 | THEODORE | SPENSER | 1980-06-19 | MANAGER |
| 000110 | VINCENZO | LUCCHESSI | 1958-05-16 | SALESREP |
| 000120 | SEAN | O'CONNELL | 1963-12-05 | CLERK |
| 000130 | DOLORES | QUINTANA | 1971-07-28 | ANALYST |
| 000140 | HEATHER | NICHOLLS | 1976-12-15 | ANALYST |
| 000160 | ELIZABETH | PIANKA | 1977-10-11 | DESIGNER |
| 000250 | JAMES | JEFFERSON | 1966-11-21 | CLERK |
| 000240 | SALVATORE | MARINO | 1979-12-05 | CLERK |
| 000330 | WING | LEE | 1976-02-23 | FIELDREP |
EMP_PROJ TABLE
| EMPNO | PROJECTNO | ACTNO | EMPTIME |
|---|---|---|---|
| 000010 | AD3110 | 10 | 1.00 |
| 00030 | AD3111 | 60 | 1.00 |
| 000020 | AD3111 | 60 | 0.50 |
| 000100 | AD3111 | 80 | 1.00 |
| 000250 | AD3112 | 60 | 1.00 |
| 000120 | AD3112 | 70 | 0.50 |
| 000250 | AD3112 | 70 | 1.00 |
| 000140 | AD3112 | 70 | 0.25 |
| 000150 | AD3113 | 70 | 0.50 |
| 000330 | AD3113 | 180 | 1.00 |
PROJECT TABLE:
| PROJECTNO | PROJECTNAME |
|---|---|
| AD3110 | CLOUD MANAGEMENT |
| AD3111 | SUSTAINABLE DEVELOPMENT |
| AD3112 | TRACKING APPLICATION |
| AD3113 | DEEP LEARNING |
Firstly we are required to join the EMP and EMP_PROJ, but we need all the records of EMP_PROJ and only matching records of the EMP table. Thus, we will be performing the right join on EMPNO field from both tables. Similarly, we need only matching records of the PROJECT table so we will now perform a left join on the PROJECT NO field from EMP_PROJ and PROJECT table. Lastly, we will add a where condition to check if EMPTIME >= 1.00.
Output:
How SQL Multiple JOINS Work?
Most Relational Database Management Systems have query optimizers that calculate the cost of each possible way of performing these joins. From data distributions and statistics gathered periodically the optimizer determines the cost. Out of the multiple optional costs typically the lowest or the most efficient query plan is executed.
Application of SQL Multiple JOINS
Multiple Join is a very important and useful concept of SQL. Very often we are required to fetch data from multiple tables based on some conditions. As seen in the above example, multiple joins are easy to implement and very frequently used when working with multiple tables. We usually have tables of every individual entity in our database, using lossless (i.e. where none of our data is lost) joins we retrieve data as per the requirement.
Conclusion
- SQL provides multiple types of joins such as INNER JOIN, LEFT JOIN, RIGHT JOIN, etc.
- We can use more than one joins of the same or different types in a single SQL query and this is known as Multiple joins in SQL.
- Before performing any joins, query optimizers calculate the cost of performing joins by various paths and return the most efficient way of performing the multiple join function.