What is a Join in SQL ?

In SQL (Structured Query Language), a join in sql is a powerful operation that combines rows from two or more tables based on a related column between them. Joins are essential for querying data from multiple tables, allowing you to retrieve and analyze data in a cohesive and comprehensive manner.

Types of Joins

There are several types of joins in SQL, each serving different purposes. The primary types include:

  1. Inner Join
  2. Left (Outer) Join
  3. Right (Outer) Join
  4. Full (Outer) Join
  5. Cross Join
  6. Self Join

Let’s delve into each type with detailed explanations and examples.

1. Inner Join

An inner join returns only the rows that have matching values in both tables. If there are no matches, the result set will not include those rows.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example:

Suppose we have two tables: `Employees` and `Departments`.

Employees Table:

EmployeeID Name DepartmentID
1 John 101
2 Alice 102
3 Bob 101

Departments Table:

DepartmentID DepartmentName
101 HR
102 IT
103 Finance

To get a list of employees along with their department names:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name DepartmentName
John HR
Alice IT
Bob HR

2. Left (Outer) Join

A left join returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name DepartmentName
John HR
Alice IT
Bob HR

3. Right (Outer) Join

A right join returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example: 

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name DepartmentName
John HR
Alice IT
Bob HR
NULL Finance

4. Full (Outer) Join

A full join returns all rows when there is a match in either table. If there is no match, NULL values are returned for columns from the table that lacks a matching row.

Syntax: 

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name DepartmentName
John HR
Alice IT
Bob HR
NULL Finance

5. Cross Join

A cross join returns the Cartesian product of two tables, combining all rows from the first table with all rows from the second table.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;

Result:

Name DepartmentName
John HR
John IT
John Finance
Alice HR
Alice IT
Alice Finance
Bob HR
Bob IT
Bob Finance

6. Self Join

A self join is a join of a table with itself. It is useful for comparing rows within the same table.

Syntax:

SELECT A.column1, B.column2
FROM table A, table B
WHERE condition;

Example:

Suppose we have an `Employees` table that includes a column `ManagerID` referencing `EmployeeID` within the same table.

Employees Table:

EmployeeID Name ManagerID
1 John NULL
2 Alice 1
3 Bob 1
4 Carol 2

To get a list of employees along with their managers:

SELECT A.Name AS Employee, B.Name AS Manager
FROM Employees A
LEFT JOIN Employees B
ON A.ManagerID = B.EmployeeID;

Result:

Employee Manager
John NULL
Alice John
Bob John
Carol Alice

Conclusion

Joins in SQL are fundamental for querying and combining data across multiple tables. Understanding the different types of joins and their applications enables you to handle complex queries and extract meaningful insights from relational databases. Each type of join serves a unique purpose and can be used to address specific data retrieval needs.

What is a Join in SQL ?

Published on 14-Dec-2021 16:51:44

You may also like this!