SQL - Introduction to Joins
SQL Joins
A JOIN clause is a statement that is used to combine data from two or more tables based on a common column between them. There are different types of Joins namely -
- Inner Join
- Left Join
- Right Join
- Full Join
Let's look at the 'Orders' table:
Now look at the 'Customers' table:
We can see that the 'Cust_ID' column in the 'Orders' table refers to the Cust_Id column in the 'Customers' table. Thus we can create a SQL statement (Inner Join) which will select the matching values in both tables:
SELECT Orders.Ord_Id, Customers.Name, Customers.City
FROM Customers
INNER JOIN Orders ON Orders.Cust_ID=Customers.Cust_Id;
It will produce the following result:
Different Types of Joins -
- Inner Join- Produce records that have matching values in both tables.
- Left Join- Will return all the records from the left table and matching records from the right table.
- Right Join- Will return all the records from the right table and matching records from the left table.
- Full Join- Will return all the records when there are matching values in either left or the right table
Inner Join
The Inner Join selects records that have matching values in both tables.
Syntax:
SELECT column_name
FROM Table1
INNER JOIN Table2
ON Table1.column_name = Table2.column_name;
Example:
SELECT Orders.Ord_Id, Customers.Name
FROM Orders
INNER JOIN Customers ON Orders.Cust_Id = Customers.Cust_ID;
It will produce the following result:
Left Join
The Left Join clause selects all the records from the left table and matching records from the right table. The result is NULL from the right side if there are no matching records from the right table.
Syntax:
SELECT column_name
FROM Table1
LEFT JOIN Table2
ON Table1.column_name = Table2.column_name;
Example:
SELECT Customers.Name, Orders.Ord_Id
FROM Customers
LEFT JOIN Orders ON Customers.Cust_ID = Orders.Cust_Id;It will produce the following result:
Right Join
The Right Join clause will select all the records from the right table and matching records from the left table. The result is NULL from the left side if there are no matching records from the left table.
Syntax:
SELECT column_name
FROM Table1
RIGHT JOIN Table2
ON Table1.column_name = Table2.column_name;
SELECT Orders.Ord_Id, Customers.Name, Customers.City
FROM Customers
RIGHT JOIN Orders ON Customers.Cust_ID = Orders.Cust_Id;It will produce the following result:
The right join clause will return all the records from the left table even if there are no matching records in the left table.
Full Join
The Full Join clause will select all the records when there are matching values in either the left or the right table.
Syntax:
SELECT column_name
FROM Table1
FULL JOIN Table2
ON Table1.column_name = Table2.column_name;
Example:
SELECT Customers.Name, Orders.Ord_Id
FROM Customers
FULL JOIN Orders ON Customers.Cust_ID=Orders.Cust_Id;It will produce the following result:
