In this article, we will have a look at an important topic in SQL, how to perform Join when there are more than two tables given .
Typically, Joins or JOIN Clauses are used to combine rows or records from two or more tables on the basis of a common or related column between them. There are many types of Joins. It is recommended to learn about them before proceeding to have a clear idea.
To show the JOIN between 3 or more tables we will consider the following schema design with tables :
Here, we have 4 tables DEPARTMENT, GENDER, PROJECT and EMPLOYEES. This design represents relationship of one parent table with other tables. The DEPARTMENT table contains the information of each Employee’s department. The GENDER Table contains information of each employee’s gender and the PROJECT table contains the details about the project an employee is a part of including the status of project. The Employee is the main table which establishes relationships between different tables.
The EMPLOYEES table has attributes ID and name for each employee. The DEPT_ID attribute gives us information about the Employee’s department. It acts as a FOREIGN KEY which references ID (PRIMARY KEY) of DEPARTMENT Table. Similarly, GENDER_ID and PROJECT_ID are foreign keys referencing ID column of GENDER and PROJECT tables respectively.
Let us look at the description of each table along with their schema definition:
DEPARTMENT
The table DEPARTMENT is shown below:
| ID | NAME |
| 1 | OPERATIONS |
| 2 | HUMAN RESOURCES |
| 3 | FINANCE |
Let us look at how to create table DEPARTMENT and insert some records in query:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE DEPARTMENT ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(20) ); INSERT INTO DEPARTMENT VALUES (1,'OPERATIONS'); INSERT INTO DEPARTMENT VALUES (2,'HUMAN RESOURCES'); INSERT INTO DEPARTMENT VALUES (3,'FINANCE'); |
GENDER
The table GENDER is shown below:
| ID | TYPE |
| 1 | MALE |
| 2 | FEMALE |
| 3 | OTHER |
Let us create the table and insert the above data:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE GENDER ( ID INTEGER PRIMARY KEY, TYPE VARCHAR2(20) ); INSERT INTO GENDER VALUES (1,'MALE'); INSERT INTO GENDER VALUES (2,'FEMALE'); INSERT INTO GENDER VALUES (3,'OTHER'); |
PROJECTS
| ID | NAME | STATUS |
| 1 | IT SUPPORT | ONGOING |
| 2 | ORIENTATION | COMPLETED |
| 3 | RISK ANALYSIS | ONGOING |
Let’s create Table PROJECTS with above records in query:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE PROJECTS ( ID INTEGER PRIMARY KEY, NAME VARCHAR2(20), STATUS VARCHAR2(20) ); INSERT INTO PROJECTS VALUES (1,'IT SUPPORT','ONGOING'); INSERT INTO PROJECTS VALUES (2,'ORIENTATION','COMPLETED'); INSERT INTO PROJECTS VALUES (3,'RISK ANALYSIS','ONGOING'); |
EMPLOYEES
| ID | NAME | DEPT_ID | GENDER_ID | PROJECT_ID |
|---|---|---|---|---|
| 1 | Amanda Jones | 2 | 2 | 2 |
| 2 | Steve Madden | 3 | 1 | 3 |
| 3 | Robert Henry | 1 | 1 | 1 |
| 4 | Shawn Adams | 1 | 3 | 1 |
| 5 | Luke Shaw | 3 | 1 | 3 |
Let us create the above table :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE EMPLOYEES ( ID INTEGER PRIMARY KEY, NAME VARCHAR2(20), DEPT_ID INTEGER, GENDER_ID INTEGER, PROJECT_ID INTEGER, FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(ID), FOREIGN KEY (GENDER_ID) REFERENCES GENDER(ID), FOREIGN KEY (PROJECT_ID) REFERENCES PROJECTS(ID) ); INSERT INTO EMPLOYEES VALUES (1,'Amanda Jones',2,2,2); INSERT INTO EMPLOYEES VALUES (2,'Steve Madden',3,1,3); INSERT INTO EMPLOYEES VALUES (3,'Robert Henry',1,1,1); INSERT INTO EMPLOYEES VALUES (4,'Shawn Adams',1,3,1); INSERT INTO EMPLOYEES VALUES (5,'Luke Shaw',3,1,3); |
Note: All the above mentioned queries are for MYSQL and ORACLE Server. For the SQL server we need to do modify INTEGER or NUMBER to INT. Also run the GO command after each Create Table statement and after all INSERT operations.
Joining the Tables to get desired result:
- Now, if we want to get Name of Employees, belonging to a Department along with their Gender, so we have to perform join between tables EMPLOYEES, DEPARTMENT and GENDER. The query will look like:
|
1 2 3 4 5 |
SELECT EMPLOYEES.NAME,DEPARTMENT.NAME AS DEPARTMENT, TYPE AS GENDER FROM EMPLOYEES JOIN DEPARTMENT ON EMPLOYEES.DEPT_ID = DEPARTMENT.ID JOIN GENDER ON EMPLOYEES.GENDER_ID = GENDER.ID ORDER BY EMPLOYEES.NAME; |
The Output of this query will be :
| NAME | DEPARTMENT | GENDER |
|---|---|---|
| Amanda Jones | HUMAN RESOURCES | FEMALE |
| Luke Shaw | FINANCE | MALE |
| Robert Henry | OPERATIONS | MALE |
| Shawn Adams | OPERATIONS | OTHER |
| Steve Madden | FINANCE | MALE |
Here, We performed join on three tables and the result was ordered on the basis of EMPLOYEE ID.
2. Now, Suppose we want to retrieve Records of Employees with their Department, Gender and the Project they are working on. The Join would look like this :
|
1 2 3 4 5 6 |
SELECT EMPLOYEES.NAME,DEPARTMENT.NAME AS DEPARTMENT, TYPE AS GENDER,PROJECTS.NAME AS PROJECT FROM EMPLOYEES JOIN DEPARTMENT ON EMPLOYEES.DEPT_ID = DEPARTMENT.ID JOIN GENDER ON EMPLOYEES.GENDER_ID = GENDER.ID JOIN PROJECTS ON EMPLOYEES.PROJECT_ID = PROJECTS.ID ORDER BY EMPLOYEES.ID; |
Output:
Now , let us write a query which will get us the Records of Employees of a Department with their Project Name and Project Status.
|
1 2 3 4 5 6 |
SELECT EMPLOYEES.NAME, DEPARTMENT.NAME AS DEPARTMENT, PROJECTS.NAME AS PROJECT, PROJECTS.STATUS FROM EMPLOYEES JOIN DEPARTMENT ON EMPLOYEES.DEPT_ID = DEPARTMENT.ID JOIN PROJECTS ON EMPLOYEES.PROJECT_ID = PROJECTS.ID ORDER BY DEPARTMENT; |
Output:
We order the result on basis of Department Name.
3. Now let’s write query to get the Number of Employees working in a Department and Project. We can use GROUP BY Clause for this, the query will be :
|
1 2 3 4 5 6 7 8 |
SELECT DEPARTMENT.NAME, PROJECTS.NAME AS PROJECT, COUNT(*) AS TOTAL_EMPLOYEES FROM EMPLOYEES JOIN DEPARTMENT ON EMPLOYEES.DEPT_ID = DEPARTMENT.ID JOIN PROJECTS ON EMPLOYEES.PROJECT_ID = PROJECTS.ID GROUP BY DEPARTMENT.NAME, PROJECTS.NAME ORDER BY DEPARTMENT.NAME; |
Output:
We grouped the result by Department name, Project , Count(*) return the number of rows in each group and ordered the final result set on basis of Department Name.
Further reading:
Join using Parent-Child Relationship
Another way to Join tables is using the Parent-Child Relationship . Let us have a quick look at how to obtain results from such a relation.
Here, we mainly use the WHERE Clause with instances of relation/table to join three or more tables. We need to create column as a primary key in one table and a foreign key in another table. Here, there is no need to explicitly use the ‘JOIN’ Keyword. The table containing the foreign keys is referred to as the ‘Child’ table. The table whose primary key is referred to from the child table is the ‘Parent’ table.
For the above example tables, the table EMPLOYEES is the Child Table containing the foreign keys which refer to the Primary Key of Parent Tables DEPARTMENT, GENDER and PROJECTS.
Now let us look at some queries to show the working:
For example like in Query 2 discussed above if we want to retrieve Records of Employees with their Department, Gender and the Project the query using the Parent- Child Relationship will look like this:
|
1 2 3 4 5 |
SELECT E.NAME,D.NAME AS DEPARTMENT,G.TYPE AS GENDER,P.NAME AS PROJECT FROM EMPLOYEES E, DEPARTMENT D, GENDER G, PROJECTS P WHERE E.DEPT_ID = D.ID AND E.GENDER_ID = G.ID AND E.PROJECT_ID= P.ID; |
Output:
Similarly for Query 3 to get the Records of Employees of a Department with their Project Name and Project Status the query will be :
|
1 2 3 4 5 |
SELECT E.NAME, D.NAME AS DEPARTMENT, P.NAME AS PROJECT, P.STATUS FROM EMPLOYEES E, DEPARTMENT D, GENDER G, PROJECTS P WHERE E.DEPT_ID = D.ID AND E.GENDER_ID = G.ID AND E.PROJECT_ID= P.ID; |
OUTPUT:
So that’s it for the article, you can try out the given examples in your SQL Compiler ( MYSQL, ORACLE or SQL Server), along with other examples to have a clear idea.
You can leave your suggestions or doubts in the comment section below.
