What are Joins in SQL? With Examples

In this article, we will explore the concept of joins in SQL and provide clear examples to illustrate their usage.

What are Joins in SQL?

JOINS in SQL are powerful commands used to combine rows from two or more tables based on a related column between those tables. They are commonly employed when extracting data from tables with one-to-many or many-to-many relationships. Let's discuss the advantages and disadvantages of using JOINS in SQL:

Advantages:

1. Fast Execution: JOINS are optimized by the database engine, making them efficient for retrieving data. The columns used for joining are typically indexed, resulting in speedier retrieval compared to subqueries.

2. Increased Performance: By combining tables using JOINS, you can reduce the number of queries executed against the database, leading to improved performance and reduced network traffic.

Disadvantages:

1. Lack of Readability: JOINS can become complex, especially when multiple tables are involved or when different types of joins are used. This complexity can make the SQL code harder to read and understand.

2. Choosing the Correct Join: Selecting the appropriate join type can sometimes be challenging, especially for newcomers to SQL. Understanding the data relationships and the desired result set is crucial for determining the correct join type to yield the desired outcome.

Types of Joins:

There are mainly four types of joins that you need to understand. They are:

• INNER JOIN

• FULL JOIN

• LEFT JOIN

• RIGHT JOIN

Consider the below tables:

Table 1: Employee

Table 2: Project

Table 3: Client

INNER JOIN

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows that satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row

Example:

SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName
FROM Employee
INNER JOIN Projects ON Employee.EmpID=Projects.EmpID;

Output:

FULL JOIN

A FULL JOIN returns all the rows from the joined tables, whether they are matched or not i.e. you can say a full join combines the functions of a LEFT JOIN and a RIGHT JOIN. A full join is a type of outer join that's why it is also referred to as full outer join.

Example:

SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID
FROM Employee
FULL JOIN Projects
ON Employee.EmpID = Projects.EmpID;

Output:

LEFT JOIN

The LEFT JOIN returns all rows from the left table, even if no matches are in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.

Example:

SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName
FROM Employee
LEFT JOIN
ON Employee.EmpID = Projects.EmpID ;

Output:

RIGHT JOIN

The RIGHT JOIN returns all rows from the right table, even if no matches are in the left table. This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with NULL in each column from the left table.

Example:

SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName
FROM Employee
RIGHT JOIN
ON Employee.EmpID = Projects.EmpID;

Output:

Conclusion

JOINS remains an essential tool for combining data from related tables efficiently. With practice and understanding, the complexities associated with JOINS can be overcome, and the benefits they offer in terms of performance and data retrieval make them invaluable in SQL query composition.