Join in Relational Database
Join in Relational Database is an important mechanism to show data from two or multiple tables. Depending upon the requirement different types of join involves combining rows from two or more tables based on a related column between them. Here’s an overview of different types of joins:
Different types of Join in Relational Database
- Inner Join: Returns only the rows that have matching values in both tables.
- Left (Outer) Join: Returns all rows from the left table, and the matched rows from the right table. If no match, the result is NULL on the side of the right table.
- Right (Outer) Join: Returns all rows from the right table, and the matched rows from the left table. If no match, the result is NULL on the side of the left table.
- Full (Outer) Join: Returns all rows when there is a match in either left or right table. If no match, the result is NULL from the table that does not have a match.
- Cross Join: Returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows.
Here are some SQL examples:
Inner Join in Relational Database
SELECT A.column1, B.column2
FROM TableA A
INNER JOIN TableB B
ON A.common_column = B.common_column;
Left (Outer) Join in Relational Database
SELECT A.column1, B.column2
FROM TableA A
LEFT JOIN TableB B
ON A.common_column = B.common_column;
Right (Outer) Join in Relational Database
SELECT A.column1, B.column2
FROM TableA A
RIGHT JOIN TableB B
ON A.common_column = B.common_column;
Cross Join in Relational Database
SELECT A.column1, B.column2
FROM TableA A
CROSS JOIN TableB B;Join Example Scenario in Relational Database
Let’s consider two tables: Employees and Departments.
Employees Table:
| EmpID | EmpName | DeptId |
| 1 | John | 10 |
| 2 | Jane | 20 |
| 3 | Bob | 30 |
Departments Table:
| DeptId | DeptName |
| 10 | Sales |
| 20 | Marketing |
| 40 | IT |
Inner Join Example in Relational Database
SELECT Employees.EmpName, Departments.DeptName
FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.DeptID;Result:
| EmpName | DeptName |
| John | Sales |
| Jane | Marketing |
Left Join Example in Relational Database
SELECT Employees.EmpName, Departments.DeptName
FROM Employees
LEFT JOIN Departments
ON Employees.DeptID = Departments.DeptID;Result:
| EmpName | DeptName |
| John | Sales |
| Jane | Marketing |
| Bob | NULL |
These join operations are fundamental to querying relational databases and allow for flexible data retrieval across multiple related tables.
Right Join Example in Relational Database
The Right Join retrieves all records from the right table (Departments), and the matched records from the left table (Employees). The result is NULL from the left side, when there is no match.
SELECT Employees.EmpName, Departments.DeptName
FROM Employees
RIGHT JOIN Departments
ON Employees.DeptID = Departments.DeptID;Result:
| EmpName | DeptName |
| John | Sales |
| Joe | Marketing |
| NULL | IT |
In this result, you can see that even though there is no employee associated with the IT department (DeptID 40), the department still appears in the results, with NULL values for the employee columns.
Full Outer Join Example in Relational Database
The Full Outer Join combines the results of both Left Join and Right Join. It retrieves all records when there is a match in either left (Employees) or right table (Departments). If there is no match, the result is NULL from the table without the match.
SELECT Employees.EmpName, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DeptID = Departments.DeptID;Result:
| EmpName | DeptName |
| John | Sale |
| Jane | Marketing |
| Bob | NULL |
| NULL | IT |
Self Join in Relational Database
A Self Join is a regular join, but the table is joined with itself. This can be useful when you want to compare rows within the same table.
For example, suppose we have an Employees table with a column ManagerID that refers to the EmpID of the manager:
Employees Table:
| EmpID | EmpName | DeptId | ManagerID |
| 1 | John | 10 | NULL |
| 2 | Jane | 20 | 1 |
| 3 | Bob | 30 | 1 |
| 4 | Alice | 10 | 2 |
To list employees along with their manager’s name, you could write:
SELECT e1.EmpName AS Employee, e2.EmpName AS Manager
FROM Employees e1
LEFT JOIN Employees e2
ON e1.ManagerID = e2.EmpID;Result:
| Employee | Manager |
| John | NULL |
| Jane | John |
| Bob | John |
| Alice | Jane |
Cross Join Example in Relational Database
The Cross Join produces the Cartesian product of the two tables involved. This means that each row from the first table is combined with all rows in the second table. It does not require any condition for joining.
SELECT Employees.EmpName, Departments.DeptName
FROM Employees
CROSS JOIN Departments;Result:
| EmpName | DeptName |
| John | Sales |
| John | Marketing |
| John | IT |
| Jane | Sales |
| Jane | Marketing |
| Jane | IT |
| Bob | Sales |
| Bob | Marketing |
| Bob | IT |
This type of join can generate a large number of rows if both tables have many rows.
Using Aliases in Joins in Relational Database
In SQL queries, especially those with joins, aliases can simplify your code and make it more readable. Aliases are temporary names given to tables or columns for the duration of a query.
Example with aliases:
SELECT e.EmpName, d.DeptName
FROM Employees e
INNER JOIN Departments d
ON e.DeptID = d.DeptID;In this example, e and d are aliases for Employees and Departments, respectively.
Combining Multiple Joins in Relational Database
You can combine multiple joins in a single query to retrieve data from more than two tables. Here’s an example that combines inner and left joins:
Assume we have an additional Salaries table:
Salaries Table:
| EmpID | Salary |
| 1 | 7000 |
| 2 | 8000 |
| 4 | 6000 |
To retrieve employee names, department names, and their salaries, you can write:
SELECT e.EmpName, d.DeptName, s.Salary
FROM Employees e
INNER JOIN Departments d
ON e.DeptID = d.DeptID
LEFT JOIN Salaries s
ON e.EmpID = s.EmpID;Result:
| EmpName | DeptName | Salary |
| John | Sales | 7000 |
| Jane | Marketing | 8000 |
| Bob | NULL | NULL |
| Alice | NULL | 600 |
Conclusion
Understanding the various types of joins and how to use them effectively is crucial for querying relational databases. Joins allow you to combine and manipulate data from multiple tables to gain comprehensive insights. Always choose the appropriate type of join based on your data retrieval requirements to ensure accurate and efficient results.
