When working with relational databases, it’s essential to understand how to combine data from different tables. This process is called joining, and it’s one of the most powerful features of a database management system (DBMS). Joins allow you to query multiple tables at once, retrieving only the data you need and presenting it in a single result set.
In this article, we’ll explore the different types of joins available in DBMS and provide examples of how to use them in SQL.
What are Joins in DBMS?
A join is a way to combine data from two or more tables based on a common column. For example, let’s say we have two tables: Customers and Orders. The Customers table contains information about each customer, including their name, address, and email address. The Orders table contains information about each order, including the order date, product name, and quantity.
To combine data from these two tables, we can join them on the customer ID column, which is common to both tables. By doing so, we can retrieve information about each customer’s orders in a single query.
Types of Joins in DBMS
There are several types of joins in DBMS, each with its own syntax and use case. The following are the different types of Joins in DBMS.
- Inner Join
- Theta Join
- Equi Join
- Natural Join
- Outer Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
We will understand all of these joins in DBMS with the help of the following tables.
Table1:
| ID | Name | Age |
|---|---|---|
| 1 | Alice | 23 |
| 2 | Bob | 28 |
| 3 | Charlie | 32 |
Table2:
| ID | Address | Salary |
|---|---|---|
| 2 | New York | 50000 |
| 3 | Boston | 65000 |
| 4 | San Diego | 75000 |
Inner Join
An Inner Join returns only the rows in both tables that match the join condition.
Theta Join
A Theta Join uses a condition other than equality to join two tables. It uses operators like <, >, <=, >=, <>, etc., to define the join condition.
Syntax of Theta Join
SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2
ON table1.columnX operator table2.columnY;Example of Theta Join
Consider the above two tables.
Query:
To perform a Theta Join, we can join the two tables on the condition that the age in Table1 is greater than the salary in Table2.
SELECT Table1.Name, Table2.Address
FROM Table1
INNER JOIN Table2
ON Table1.Age > Table2.Salary;Output:
This query would return the following result:
| Name | Address |
|---|
Here we get an empty table as a result because not a single entry is following the condition specified in the join
Equi Join
An Equi Join returns all the rows in both tables where the specified
columns are equal.
Syntax of Equi Join
SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2
ON table1.columnX = table2.columnY;Example of Equi Join
Taking reference from the above tables, table 1 and table 2.
Query:
To perform an Equi Join, we can join the two tables on the ID column.
SELECT Table1.Name, Table2.Address
FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID;Output:
This query would return the following result:
| Name | Address |
|---|---|
| Bob | New York |
| Charlie | Boston |
Natural Join
A Natural Join is a type of Join that matches columns with the same name in both tables.
Syntax of Natural Join
SELECT table1.column1, table2.column2
FROM table1
NATURAL JOIN table2;Example of Natural Join
Consider the above two tables:
Query:
To perform a Natural Join, we can simply use the following query:
SELECT Table1.ID, Table1.Name, Table1.Age, Table2.Address, Table2.Salary
FROM Table1
NATURAL JOIN Table2;Output:
This query would return the following result:
| ID | Name | Age | Address | Salary |
|---|---|---|---|---|
| 2 | Bob | 28 | New York | 50000 |
| 3 | Charlie | 32 | Boston | 65000 |
Outer Join
An Outer Join in DBMS returns all the rows from one table and the matching rows from the other table. If there is no match, NULL values are returned for the missing rows.
Left Outer Join
A Left Outer Join in DBMS returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the missing rows.
Syntax of Left Outer Join
SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2
ON table1.columnX = table2.columnY;Example of Left Outer Join
Again Considering the above two tables:
Query:
To perform a Left Outer Join, we can join the two tables on the ID column.
SELECT Table1.Name, Table2.Address
FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.ID;Output:
This query would return the following result:
| Name | Address |
|---|---|
| Alice | NULL |
| Bob | New York |
| Charlie | Boston |
Right Outer Join
A Right Outer Join returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the missing rows.
Syntax of Right Outer Join
SELECT table1.column1, table2.column2
FROM table1
RIGHT JOIN table2
ON table1.columnX = table2.columnY;Example of Right Outer Join
Consider the above two tables:
Query:
To perform a Right Outer Join, we can join the two tables on the ID column.
SELECT Table1.Name, Table2.Address
FROM Table1
RIGHT JOIN Table2
ON Table1.ID = Table2.ID;Output:
This query would return the following result:
| Name | Address |
|---|---|
| Bob | New York |
| Charlie | Boston |
| NULL | San Diego |
Full Outer Join
A Full Outer Join returns all the rows from both tables and NULL values for the missing rows.
Syntax of Full Outer Join
SELECT table1.column1, table2.column2
FROM table1
FULL OUTER JOIN table2
ON table1.columnX = table2.columnY;Example of Full Outer Join
Considering the above-mentioned two tables i.e., Table1 and Table2:
Query:
To perform a Full Outer Join, we can join the two tables on the ID column.
SELECT Table1.Name, Table2.Address
FROM Table1
FULL OUTER JOIN Table2
ON Table1.ID = Table2.ID;Output:
This query would return the following result:
| Name | Address |
|---|---|
| Alice | NULL |
| Bob | New York |
| Charlie | Boston |
| NULL | San Diego |
A Quick Revision of Joins in DBMS
To assist you in a good revision, a quick revision on Joins in DBMS is given below:
- Joins in DBMS is used to combine tables.
- There are three types of joins: inner joins, natural joins, and outer joins.
- Inner joins are classified into two types: Theta Join(for relational operators) and Equi Join(for Equality).
- There are three types of outer joins in DBMS: left outer join, right outer join, and full outer join.
- Natural join is only performed when at least one matching attribute exists in both tables.
- No matter the Join condition, a left outer join always returns every row from the left table.
- Regardless of the Join condition, Right Outer Join always returns all rows from the right table.
- Regardless of the join condition, Complete Outer Join always returns all rows from both tables.
Conclusion
Joins in DBMS are an essential feature that allows you to combine data from multiple tables into a single result set. There are several types of joins available, each with its own syntax and use case. We have learned all the different types of joins in DBMS. By understanding how to use joins in SQL, you can retrieve only the data you need and present it in a meaningful way.
Joins in DBMS – FAQs
Some Frequently Asked Questions on Joins in DBMS are given below.
Ques 1. What is the difference between Inner Join and Outer Join?
Ans. The main difference between Inner Join and Outer Join is that Inner Join returns only the matching records from both tables, while Outer Join returns all records from one table and matching records from the other table.
Ques 2. What is the order of execution of Joins in a SQL statement?
Ans. Joins are executed from left to right in a SQL statement. The first table specified in the FROM clause is joined with the second table, and the result is joined with the third table, and so on.
Ques 3. How do you optimize Joins in DBMS?
Ans. To optimize Joins in DBMS, you can use techniques such as Indexing, Query optimization, and Denormalization. Indexing can help in faster search operations, Query optimization can help in optimizing the query execution plan, and Denormalization can help in reducing the number of Joins required.