SQL Joins

SQL Joins combine two or more tables together based on the corresponding common column among them.

Note: All the below mentioned queries are executed with reference to MySQL Database.

Types of SQL Joins

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• SELF JOIN

1. INNER JOIN

SQL INNER JOIN basically serves as the intersection of two or more tables. It returns elements that are common to both of the tables.

Let’s use the following tables to serve and understand the purpose of SQL Joins.

Table name: Information

Table name: Manager_Details

Syntax:

SELECT Column_Names
FROM Table1
INNER JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;

Example:

SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
INNER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;

Output:

1	1	Safa Mulani
2	2	Aman Mulani

2. LEFT OUTER JOIN

SQL LEFT OUTER JOIN returns all the elements from the left table(i.e. Table1) and the corresponding matched elements from the right table(i.e. Table2).

If the appropriate match isn’t found, it returns NULL values for the unmatched right table column values.

Syntax:

SELECT Column_Names
FROM Table1
LEFT JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;

Example:

SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
LEFT JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;

Output:

1	1	Safa Mulani
2	2	Aman Mulani
3	NULL	NULL

3. RIGHT OUTER JOIN

SQL RIGHT OUTER JOIN returns all the elements from the right table(i.e. Table2) and the corresponding matched elements from the left table(i.e. Table1).

If the appropriate match isn’t found, it returns NULL values for the unmatched left table column values.

Syntax:

SELECT Column_Names
FROM Table1
RIGHT JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;

Example:

SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
RIGHT JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;

Output:

1	1	Safa Mulani
2	2	Aman Mulani
NULL	3	Divya Trivedi

4. FULL OUTER JOIN

SQL FULL JOIN basically represents all the elements which either matches the elements from the left table or the right table.

If the rows of either of the tables do not match each other, still the elements will be represented with NULL values attached to it.

Syntax:

SELECT Column_Names
FROM Table1
FULL OUTER JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;

Most of the Databases such as SQLite, MySQL, etc do not support FULL OUTER Joins.

In order to serve the purpose, we need to modify the query and make use of UNION clause to provide the functionalities of the FULL OUTER JOIN.

Syntax:

SELECT Column_Names
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.Column_Name = Table2.Column_Name
UNION
SELECT Column_Names
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;

Example:

SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
LEFT OUTER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id
UNION
SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
RIGHT OUTER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;

Output:

1	1	Safa Mulani
2	2	Aman Mulani
3	NULL	NULL
NULL	3	Divya Trivedi

5. SELF JOIN

SQL SELF JOIN represents a join to itself. i.e it renames the table, gives alias names to the corresponding table.

Syntax:

SELECT Column_Name
FROM Table1 A, Table1 B
WHERE condition;

Here, A and B are the alias names given to the same Table1.

Example:

SELECT  a.Manager_Id, b.Name, a.City    
FROM Manager_details a, Manager_details b
WHERE a.Age > 24 AND b.Age > 24;

Output:

1	Safa Mulani	Pune
2	Safa Mulani	Satara
1	Aman Mulani	Pune
2	Aman Mulani	Satara

Conclusion

Thus, in this article, we have understood the functionalities of SQL Joins.

Reference

• MySQL Documentation