In a relational database, a join operation combines data from two or more tables based on a related column or condition. It allows you to retrieve information from multiple tables as if they were one, helping you obtain meaningful insights from your data. There are different types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Consider the following examples:

Table1: Customer

Table2: Orders

Inner Join:

The INNER JOIN returns only the rows where there is a match in both tables based on the specified condition.

SQL Query:

SELECT Customers.Name, Orders.OrderID

FROM Customers

INNER JOIN Orders ON Customers.ID = Orders.CustomerID;

Output:

Left Join:

The LEFT JOIN returns all the rows from the left table (Customers) and the matching rows from the right table (Orders). If there’s no match in the right table, NULL values will be shown.

SQL Query:

SELECT Customers.Name, Orders.OrderID

FROM Customers

LEFT JOIN Orders ON Customers.ID = Orders.CustomerID;

Output:

Right Join:

The RIGHT JOIN returns all the rows from the right table (Orders) and the matching rows from the left table (Customers). If there’s no match in the left table, NULL values will be shown.

SQL Query:

SELECT Customers.Name, Orders.OrderID

FROM Customers

RIGHT JOIN Orders ON Customers.ID = Orders.CustomerID;

Output:

Full Join:

The FULL JOIN returns all the rows when there is a match in either the left or right table. If there’s no match, NULL values will be shown.

SQL Query:

SELECT Customers.Name, Orders.OrderID

FROM Customers

FULL JOIN Orders ON Customers.ID = Orders.CustomerID;

Output: