RDBMS SQL Joins

SQL Joins in RDBMS

As databases grow, information is usually stored in multiple tables instead of one large table. This makes the database more organized and reduces duplicate data.

However, there are times when you need information from two or more tables at the same time. This is where SQL joins become useful.

An SQL join combines related data from multiple tables based on a common column, such as a primary key and a foreign key.

For example, customer details may be stored in one table, while order details are stored in another. By using a join, you can display the customer's name along with the products they ordered.

Why Do We Need Joins?

Imagine you have the following two tables.

Customers Table

Orders Table

The Orders table only stores the CustomerID, not the customer's name. If you want to display both the customer's name and the product they ordered, you need to combine data from both tables using a join.

What is an SQL Join?

An SQL join matches rows from two or more tables using a related column.

The common column is usually a primary key in one table and a foreign key in another table. SQL compares these matching values and returns the combined result.

Types of SQL Joins

There are several types of joins, and each one serves a different purpose.

INNER JOIN

An INNER JOIN returns only the rows where matching values exist in both tables.

SELECT Customers.Name,
       Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Only customers who have placed orders appear in the result.

LEFT JOIN

A LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no matching record, SQL displays NULL for the missing values.

SELECT Customers.Name,
       Orders.Product
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Even though Aman has not placed an order, his record still appears.

RIGHT JOIN

A RIGHT JOIN works opposite to a LEFT JOIN. It returns all rows from the right table and only matching rows from the left table.

SELECT Customers.Name,
       Orders.Product
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

If an order exists without matching customer information, the customer columns will contain NULL.

FULL OUTER JOIN

A FULL OUTER JOIN returns all records from both tables. Matching rows are combined, while non-matching rows show NULL in the missing columns.

SELECT Customers.Name,
       Orders.Product
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Not every database system supports FULL OUTER JOIN, but the concept is useful to understand.

Visual Understanding of Joins

You can think of joins like two overlapping circles.

• INNER JOIN returns only the overlapping part.
• LEFT JOIN returns everything from the left side plus the matching part.
• RIGHT JOIN returns everything from the right side plus the matching part.
• FULL OUTER JOIN returns everything from both sides.

Best Practices for Using Joins

When working with joins, keep these tips in mind:

• Join tables using related columns such as primary keys and foreign keys.
• Select only the columns you need instead of using SELECT * whenever possible.
• Use meaningful table names and aliases for better readability.
• Make sure relationships between tables are properly defined before writing joins.
• Test your queries with sample data to verify the results.