Introduction:

Database is a collection of inter-related data. Table is an entity which describes uniquely what it belongs to. Keys are the Columns which are used to identify the record uniquely that are called Primary Keys which must be unique within the table. So, Tables are inter-related to each other with keys.

The data has been split across with different tables which helps to remove the redundancy, and partial dependency which is Normalization.

Well. Let’s directly dive into the JOINS.

Why JOINS are needed?

Since the data has been split across into different tables, when the business needs the data to be retrieved which belongs to many tables, JOINS are needed.

The below are the tables which we are going to use it through over this article.

Schema:

Table: Dept

Table: Employee

Types of JOINS

• CROSS JOIN
• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• SELF JOIN

CROSS JOIN

A cross join returns the Cartesian product of the sets of records from the two joined tables. Generally it denotes as m x n, where m and n are tables.

Query:

SELECT
    d.DeptId, 
    d.DeptName, 
    e.EmployeeId, 
    e.EmployeeName,	
    e.DeptId 
FROM Dept d CROSS JOIN Employee e
        

How it works:

Every record in Dept table combines each and every record in Employee table.

Number of rows in Dept table: 4
Number of rows in Employee table: 5

m x n = 4 x 5 = 20 rows

Output:

INNER JOIN

An inner join combines the records from two tables based on the columns which makes related. For example, DeptId is the common field which relates Dept and Employee table.

Query:

Retrieve the Employee Id, Name with their respective department names.

SELECT 
    e.EmployeeId, 
    e.EmployeeName, 
    d.DeptName 
FROM Dept d 
INNER JOIN Employee e ON d.DeptId = e.DeptId 

How it works:

1. Retrieve the Cartesian product of Dept and Employee table which combines both the tables.
2. Apply the predicates which extract only matched rows.
3. Select the columns which you required from the result set.

The above figure explains the only blue rounded rows are matched with the relationship column (DeptId).

Output:

OUTER JOIN:

The outer join does not require the matching record in both the joined tables. The joined table retains each record even there is no match exists. Outer joins are divided into three such as LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

LEFT OUTER JOIN:

From the name, we could identify that there is some preference for the left hand side. The results of left outer join for tables Dept and Employee returns all the records from Dept table, even if the join condition does not find any match in Employee table.

Query:

SELECT 
    d.DeptId, 
    d.DeptName, 
    e.EmployeeId, 
    e.EmployeeName,	
    e.DeptId 
FROM Dept d 
LEFT OUTER JOIN Employee e ON d.DeptId = e.DeptId 

Output:

The above picture explains that each DeptId matches with Employee table except DeptId 14 which implies the employee record return as NULL.

Question:
Let say, if I want to know the Departments which does not have any employees. How can I achieve it?
You can refer the above picture also.

Query: Option 1 by using JOIN

SELECT 
    d.DeptId, 
    d.DeptName 
FROM Dept d 
LEFT OUTER JOIN Employee e ON d.DeptId = e.DeptId 
WHERE 
    e.DeptId IS NULL 

Good. We need to add the condition to check the value is NULL in WHERE Clause. You can solve in some other way also by using NOT EXISTS and NOT IN. How..? Check it out below:

Query: Option 2 by using NOT EXISTS

SELECT 
    DeptId, 
    DeptName 
FROM Dept d 
WHERE NOT EXISTS (SELECT 1 FROM Employee e WHERE d.DeptId = e.DeptId)

Do you want to know one more way?

Query: Option 3 by using NOT IN

SELECT 
    DeptId, 
    DeptName 
FROM Dept 
WHERE DeptId NOT IN(SELECT DeptId FROM Employee)

All the above three queries would return with the same result. But, you want to know which one is best. Well. Definitely, NOT IN is not a good choice. When you run with millions of records you can find the LEFT JOIN would be the better option compared with NOT EXISTS. This is extra point na, Send me Choclate. :-)

RIGHT OUTER JOIN:

A right outer join is almost same as left outer join except with the tables reversed. From the name, there is some preference for Right Side. The results of right outer join for tables Dept and Employee returns all the records from Employee, even if the join condition does not find any match in Dept table.

Query:

SELECT 
	d.DeptId, 
	d.DeptName, 
	e.EmployeeId, 
	e.EmployeeName,	
	e.DeptId 
FROM Dept d 
RIGHT OUTER JOIN Employee e ON d.DeptId = e.DeptId

Output:

FULL OUTER JOIN:

A Full outer join combines the result of both left and right outer joins which means both of the table’s rows will be retrieved and NULL will be filled up wherever missing matches.

SELF JOIN:

We have seen other types of joins by relating with different tables. There might be the situation where the table needs to relate with itself called Self Join. Joining a table itself will be useful when you want to compare the values in a column with another column values within a table.

To create a self-join, list a table twice and assign different aliases each time.

Question:
Find out the Manager Names for each Employee.

Query:

SELECT 
    Emp1.EmployeeId AS 'Employee ID', 
    Emp1.EmployeeName AS 'Employee Name', 
    Emp2.EmployeeId AS 'Manager ID',  
    Emp2.EmployeeName AS 'Manager Name' 
FROM EMPLOYEE Emp1 
INNER JOIN EMPLOYEE Emp2 ON Emp1.ManagerId = Emp2.EmployeeId

Output:

Now, if you look at the above picture, there are only four employees returned instead of five. Do you know the reason? Yeah. Since the Manager Id is NULL for the Employee ‘Anand’, which does not return. Why it’s happening? Because we used the INNER JOIN.

Since the requirement is displaying all the Employee’s Managers, we need to display the employee “Anand” also.

Query:

SELECT 
    Emp1.EmployeeId AS 'Employee ID', 
    Emp1.EmployeeName AS 'Employee Name', 
    ISNULL(Emp2.EmployeeId, 0) AS 'Manager ID', 
    ISNULL(Emp2.EmployeeName, 'Chief') AS 'Manager Name' 
FROM EMPLOYEE Emp1 
LEFT JOIN EMPLOYEE Emp2 ON Emp1.ManagerId = Emp2.EmployeeId

Since we have seen the Group by Clause in previous article, let me add one more Query which includes JOIN with Group by clause. Click here to access the GROUP BY clause article.

Question:
Retrieve the number of Employees working for each Dept, and the display order should be descending order of the employee count.

Query:

SELECT 
	d.DeptName,
	COUNT(e.DeptId) AS 'Number of Employees'
FROM Dept d
LEFT JOIN Employee e ON d.DeptId = e.DeptId
GROUP BY 
d.DeptId, d.DeptName
ORDER BY COUNT(e.DeptId) DESC

Conclusion:
I believe this article which helps you to understand about Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, and self join. Try to write as many queries as possible to understand more. You would enjoy it…! Cheers...