In a database management system (DBMS), joining tables is a fundamental operation that is used to combine data from two or more tables into a single result set. Joining tables allows users to extract relevant data by combining related data from different tables. In this guide, we'll explore different types of joins and how to use them in a DBMS.
📙 Understanding Joins
A join is an operation that combines two or more tables based on a related column or set of columns. When two tables are joined, the result set contains all the columns from both tables, with rows that satisfy the join condition. The join condition is usually based on a column or set of columns that are common between the tables.
📙Types of Joins
There are several types of joins, including:
♦ Inner Join
An inner join returns only the matching rows between two tables based on a specified condition. It means that only the records that have a match in both tables will be returned in the result set.
Example: Suppose we have two tables, Customers and Orders. Customers table has customer details such as CustomerID, Name, Email, and Orders table has OrderID, CustomerID, Product, and Quantity. We can join these tables using the common field CustomerID to get the details of customers who placed an order.
Customers Table
| CustomerID | Name | Email |
|------------|------------|-----------------------|
| 1 | John Smith | john.smith@example.com |
| 2 | Jane Doe | jane.doe@example.com |
| 3 | Bob Johnson| bob.johnson@example.com|
Orders Table
| OrderID | CustomerID | Product | Quantity |
|---------|-----------|---------|----------|
| 1001 | 1 | Chair | 2 |
| 1002 | 2 | Table | 1 |
| 1003 | 3 | Lamp | 3 |
| 1004 | 1 | Desk | 1 |
The SQL query for this join would be:
SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The output of this join will be:
| Name | Product |
|------------|---------|
| John Smith | Chair |
| John Smith | Desk |
| Jane Doe | Table |
| Bob Johnson| Lamp |
♦ Theta Join
Theta join is a type of join where the condition used to join the tables is not the equality operator. Instead, any operator such as '<', '>', '<=', '>=', or '<>' can be used to join tables.
Example: Suppose we have two tables, Employees and Salaries. Employees table has details such as EmployeeID, Name, Age, and Salaries table has EmployeeID and Salary. We can join these tables using the condition that employees who earn more than $5000 and are younger than 30 years old.
Employees Table
| EmployeeID | Name | Age |
|------------|-------------|-----|
| 1 | John Smith | 25 |
| 2 | Jane Doe | 35 |
| 3 | Bob Johnson | 28 |
Salaries Table
| EmployeeID | Salary |
|------------|--------|
| 1 | 6000 |
| 2 | 4500 |
| 3 | 5500 |
The SQL query for this join would be:
SELECT Employees.Name, Salaries.Salary
FROM Employees
INNER JOIN Salaries
ON Employees.EmployeeID = Salaries.EmployeeID
WHERE Salaries.Salary > 5000 AND Employees.Age < 30;
The output of this join will be:
| Name | Salary |
|------------|--------|
| John Smith | 6000 |
| Bob Johnson| 5500 |
♦ Equi Join
An equi join is a type of join where the condition used to join tables is the equality operator. It means that the join is performed between two tables based on a common column.
Example: Suppose we have two tables, Customers and Orders. Customers table has customer details such as CustomerID, Name, Email, and Orders table has OrderID, CustomerID, Product, and Quantity. We can join these tables using the common field CustomerID to get the details of customers who placed an order.
Customers Table
| CustomerID | Name | Email |
|------------|------------|-----------------------|
| 1 | John Smith | john.smith@example.com |
| 2 | Jane Doe | jane.doe@example.com |
| 3 | Bob Johnson| bob.johnson@example.com|
Orders Table
| OrderID | CustomerID | Product | Quantity |
|---------|-----------|---------|----------|
| 1001 | 1 | Chair | 2 |
| 1002 | 2 | Table | 1 |
| 1003 | 3 | Lamp | 3 |
| 1004 | 1 | Desk | 1 |
The SQL query for this join would be:
SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The output of this join will be:
| Name | Product |
|------------|---------|
| John Smith | Chair |
| John Smith | Desk |
| Jane Doe | Table |
| Bob Johnson| Lamp |
♦ Natural Join (⋈)
Natural join is a type of join where the join is performed based on the columns with the same name in both tables. It means that only the columns with the same name are joined.
Example: Suppose we have two tables, Customers and Orders. Customers table has customer details such as CustomerID, Name, Email, and Orders table has OrderID, CustomerID, Product, and Quantity.
Customers Table
| CustomerID | Name | Email |
|------------|------------|-----------------------|
| 1 | John Smith | john.smith@example.com |
| 2 | Jane Doe | jane.doe@example.com |
| 3 | Bob Johnson| bob.johnson@example.com|
Orders Table
| OrderID | CustomerID | Product | Quantity |
|---------|-----------|---------|----------|
| 1001 | 1 | Chair | 2 |
| 1002 | 2 | Table | 1 |
| 1003 | 3 | Lamp | 3 |
| 1004 | 1 | Desk | 1 |
The SQL query for this join would be:
SELECT *FROM Customers
NATURAL JOIN Orders;
The output of this join will be:
| CustomerID | Name | Email | OId | Pro |Qty |
|------------|------------|------------------------|-----|-------- |-----|
| 1 | John Smith | john.smith@example.com | 1001| Chair | 2 |
| 1 | John Smith | john.smith@example.com | 1004| Desk | 1 |
| 2 | Jane Doe | jane.doe@example.com | 1002| Table | 1 |
| 3 | Bob Johnson| bob.johnson@example.com| 1003| Lamp | 3 |
♦ Outer Join
Outer join is a type of join that returns all the rows from one table and the matching rows from another table. If there is no match in the second table, the result will contain NULL values.
Example: Suppose we have two tables, Customers and Orders. Customers table has customer details such as CustomerID, Name, Email, and Orders table has OrderID, CustomerID, Product, and Quantity.
Customers Table
| CustomerID | Name | Email |
|------------|------------|-----------------------|
| 1 | John Smith | john.smith@example.com |
| 2 | Jane Doe | jane.doe@example.com |
| 3 | Bob Johnson| bob.johnson@example.com|
Orders Table
| OrderID | CustomerID | Product | Quantity |
|---------|-----------|---------|----------|
| 1001 | 1 | Chair | 2 |
| 1002 | 2 | Table | 1 |
| 1003 | 3 | Lamp | 3 |
The SQL query for this join would be:
SELECT Customers.Name, Orders.Product
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The output of this join will be:
| Name | Product |
|------------|---------|
| John Smith | Chair |
| John Smith | Desk |
| Jane Doe | Table |
| Bob Johnson| Lamp |
| NULL | NULL |
In this example, the LEFT OUTER JOIN returns all the rows from the Customers table and the matching rows from the Orders table. In this case, there is no matching row for the customer with CustomerID 3 in the Orders table, so the result contains a row with NULL values.
♦ Left Outer Join
Left outer join is a type of outer join that returns all the rows from the left table and the matching rows from the right table. If there is no match in the right table, the result will contain NULL values.
Example: Suppose we have two tables, Customers and Orders. Customers table has customer details such as CustomerID, Name, Email, and Orders table has OrderID, CustomerID, Product, and Quantity.
Customers Table
| CustomerID | Name | Email |
|------------|------------|-----------------------|
| 1 | John Smith | john.smith@example.com |
| 2 | Jane Doe | jane.doe@example.com |
| 3 | Bob Johnson| bob.johnson@example.com|
Orders Table
| OrderID | CustomerID | Product | Quantity |
|---------|-----------|---------|----------|
| 1001 | 1 | Chair | 2 |
| 1002 | 2 | Table | 1 |
| 1003 | 3 | Lamp | 3 |
The SQL query for this join would be:
SELECT Customers.Name, Orders.Product
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The output of this join will be:
| Name | Product |
|------------|---------|
| John Smith | Chair |
| Jane Doe | Table |
| Bob Johnson| Lamp |
In this example, the LEFT OUTER JOIN returns all the rows from the Customers table and the matching rows from the Orders table. In this case, there is no matching row for the customer with CustomerID 3 in the Orders table, but it still returns the row from the Customers table with the corresponding NULL value in the Orders.Product column.
♦ Right Outer Join
Right outer join is a type of outer join that returns all the rows from the right table and the matching rows from the left table. If there is no match in the left table, the result will contain NULL values.
Example: Suppose we have two tables, Customers and Orders. Customers table has customer details such as CustomerID, Name, Email, and Orders table has OrderID, CustomerID, Product, and Quantity.
Customers Table
| CustomerID | Name | Email |
|------------|------------|-----------------------|
| 1 | John Smith | john.smith@example.com |
| 2 | Jane Doe | jane.doe@example.com |
| 3 | Bob Johnson| bob.johnson@example.com|
Orders Table
| OrderID | CustomerID | Product | Quantity |
|---------|-----------|---------|----------|
| 1001 | 1 | Chair | 2 |
| 1002 | 2 | Table | 1 |
| 1003 | 3 | Lamp | 3 |
The SQL query for this join would be:
SELECT Customers.Name, Orders.Product
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The output of this join will be:
| Name | Product |
|-------------|---------|
| John Smith | Chair |
| Jane Doe | Table |
| Bob Johnson | Lamp |
| NULL | Desk |
In this example, the RIGHT OUTER JOIN returns all the rows from the Orders table and the matching rows from the Customers table. In this case, there is no matching row for the order with OrderID 1004 in the Customers table, but it still returns the row from the Orders table with the corresponding NULL value in the Customers.Name column.
♦ Full Outer Join (A B)
Full outer join is a type of outer join that returns all the rows from both tables, including the matching and non-matching rows. If there is no match in one of the tables, the result will contain NULL values for the columns of that table.
Example: Suppose we have two tables, Customers and Orders. Customers table has customer details such as CustomerID, Name, Email, and Orders table has OrderID, CustomerID, Product, and Quantity.
Customers Table
| CustomerID | Name | Email |
|------------|------------|-----------------------|
| 1 | John Smith | john.smith@example.com |
| 2 | Jane Doe | jane.doe@example.com |
| 3 | Bob Johnson| bob.johnson@example.com|
Orders Table
| OrderID | CustomerID | Product | Quantity |
|---------|-----------|---------|----------|
| 1001 | 1 | Chair | 2 |
| 1002 | 2 | Table | 1 |
| 1003 | 3 | Lamp | 3 |
The SQL query for this join would be:
SELECT Customers.Name, Orders.Product
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The output of this join will be:
| Name | Product |
|-------------|---------|
| John Smith | Chair |
| Jane Doe | Table |
| Bob Johnson | Lamp |
| NULL | Desk |
| NULL | Book |
In this example, the FULL OUTER JOIN returns all the rows from both tables, including the matching and non-matching rows. In this case, there is no matching row for the order with OrderID 1004 in the Customers table and no matching row for the customer with CustomerID 4 in the Orders table, but it still returns those rows with the corresponding NULL values in the other table's columns.
📙Advantages of Join
There are several advantages of using joins in database queries, including:
Reduced data redundancy: Joining tables eliminates the need to store the same data in multiple tables. This reduces the amount of storage space required and makes it easier to maintain the data.
Improved data consistency: By storing related data in separate tables and joining them together, data consistency is improved. This ensures that data is accurate and up-to-date.
Improved query performance: Joining tables allows you to retrieve data from multiple tables with a single query. This can significantly improve query performance, especially when dealing with large datasets.
Increased flexibility: By joining tables together, you can create complex queries that can retrieve data from multiple tables based on specific criteria.
Better data analysis: Joining tables allows you to analyze data from multiple tables at once, which can help you gain a deeper understanding of your data and identify patterns and trends.
Overall, using joins can help you to create more efficient and effective database queries, which can lead to better data analysis and decision-making.
Thanks for reading, and happy coding!
Mastering DBMS Joins: Joining Tables in a Database Management System -> DBMS Indexing: Maximizing Efficiency and Speed of Data Retrieval