Database Optimization Articles

09: Introduction to the SQL Join concepts

Introduction to the SQL Join concepts

As the importance of data continues to grow exponentially, so does the amount of data. And data management is the most important factor for any organization, big or small. Hence, a very important part of normal functioning is a database management system.

Owing to the ever-increasing amount of data, the databases have multiple tables. It is highly unlikely that the user will refer to a single table. Multiple tables need to be accessed. In a relational database management system, a simple way to access multiple tables is through the use of joins. Let’s look at joins and its types with a few examples.

What is a Join?

Whenever data from different tables need to be accessed, joins are used to link data between different rows based on related columns of a database. A simple example would be retrieving different details of an individual such as phone number, address, and age against the Name of an individual.

Why are Joins important?

With the multiple data fields and types, working with multiple tables is necessary. Without the access to joins, it will be extremely difficult to retrieve multiple data entries. They also form the basis for a relational database. For any relational DBMS, it is very important to relate tables. Joins is the perfect solution to that problem.

What are the different types of SQL Joins?

Joins have multiple types of functionalities. And hence, different situations call for different types of joins. There is majorly four type of joins which are used. Let’s have a look at these and understand.

1. Inner Join

Consider a Venn diagram with two different sets, namely, A and B. The intersection between the two sets A and B (A ∩ B) is the condition of the join. An inner join will consist of the selection of this common area. In technical terms, the inner join will return all the rows that fulfill the condition of the join from two or more tables.

Syntax:

SELECT columns
FROM A
INNER JOIN B
ON A.columnname = B.columnname

2. Left SQL Join

Also known as the left outer join, this is the join which focuses on the left-hand table. Consider a Venn diagram with A and B. The left join will include the output from entire set A and the intersection of A and B (A ∩ B). In a DBMS, a left join will retrieve all rows from the left side table and only those from the right which fulfill the criteria of the join.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname

Left Outer join without intersection: Another subtype of the left join is where the rows returned include all rows from the left side table which also meet the condition of the SQL join but none of the rows from the right side table fulfilling the conditions of the joins.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE B.columnname IS NULL

3. Right SQL Join

Also known as right outer join, this conditional join focuses on the right side table. The right join returns all the rows from the right side table and the rows from the left side tables that fulfill the conditions of the join.

Syntax:

SELECT columns
FROM A
RIGHT OUTER JOIN B
ON A.columnname = B.columnname

Right Outer Join Without intersection: Similar to the left outer join without intersection, this join retrieves all the rows from tables on the right side except those fulfilling the condition of the joint from the left.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE A.columnname IS NULL

4. Full Join

Also known as Full outer join, retrieves all the values from both the tables mentioned in the ON condition. Rows from tables on either side are retrieved where the join condition is not met. With a Venn diagram, it includes data from the entire set of A and B include the unions. This includes all NULL Values.

Syntax:

SELECT columns
FROM A
FULL JOIN B
ON A.columnname = B.columnname

Full Join without intersection: Similar to a full SQL join, this retrieves all the rows from either side of the tables mentioned in the ON condition where the join condition is not met. But, this excludes all the NULL Values, unlike a FULL OUTER JOIN.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE A.columnname IS NULL
OR B.columnname is NULL

Conclusion

The above article explained the different types of SQL joins used in the relational DBMS environment. But, it also emphasizes on the fact that joins are an essential part of the RDBMS as they enable the relational part of the system by interconnecting different tables and their data.