Methods and Implementation of join operation with some example
The join operation in database systems combines rows from two or more tables based on a related column between them. There are several methods to perform a join operation, including:
1. Inner Join: The inner join returns only the rows where there is a match between the columns in both tables. It combines the matching rows into one result set.
Example:
Table 1 (Customers):
CustomerID | CustomerName
-------------------------
1 | John
2 | Mary
3 | Jerry
Table 2 (Orders):
OrderID | CustomerID | OrderDate
-------------------------------
1 | 1 | 2021-01-01
2 | 2 | 2021-02-01
3 | 1 | 2021-03-01
Query:
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Result:
CustomerName | OrderID | OrderDate
---------------------------------
John | 1 | 2021-01-01
Mary | 2 | 2021-02-01
John | 3 | 2021-03-01
2. Left Join: The left join returns all the rows from the left table and the matched rows from the right table. If there is no match, it will return NULL values for the right table columns.
Example:
Table 1 (Customers):
CustomerID | CustomerName
-------------------------
1 | John
2 | Mary
3 | Jerry
Table 2 (Orders):
OrderID | CustomerID | OrderDate
-------------------------------
1 | 1 | 2021-01-01
2 | 2 | 2021-02-01
Query:
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Result:
CustomerName | OrderID | OrderDate
---------------------------------
John | 1 | 2021-01-01
Mary | 2 | 2021-02-01
Jerry | NULL | NULL
3. Right Join: The right join returns all the rows from the right table and the matched rows from the left table. If there is no match, it will return NULL values for the left table columns.
Example:
Table 1 (Customers):
CustomerID | CustomerName
-------------------------
1 | John
2 | Mary
Table 2 (Orders):
OrderID | CustomerID | OrderDate
-------------------------------
1 | 1 | 2021-01-01
2 | 2 | 2021-02-01
3 | 3 | 2021-03-01
Query:
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Result:
CustomerName | OrderID | OrderDate
---------------------------------
John | 1 | 2021-01-01
Mary | 2 | 2021-02-01
NULL | 3 | 2021-03-01
4. Full Outer Join: The full outer join returns all the rows from both tables, including the unmatched rows. If there is no match, it will return NULL values for the respective columns.
Example:
Table 1 (Customers):
CustomerID | CustomerName
-------------------------
1 | John
2 | Mary
Table 2 (Orders):
OrderID | CustomerID | OrderDate
-------------------------------
1 | 1 | 2021-01-01
2 | 2 | 2021-02-01
3 | 3 | 2021-03-01
Query:
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Result:
CustomerName | OrderID | OrderDate
---------------------------------
John | 1 | 2021-01-01
Mary | 2 | 2021-02-01
NULL | 3 | 2021-03-01
NULL | NULL | NULL