Loading, please wait...

A to Z Full Forms and Acronyms

What are SQL Joins ?

Jul 15, 2020 SQL, Joins, 1376 Views
Introduction to Joins

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 -

  1. Inner Join
  2. Left Join
  3. Right Join
  4. 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 -

  1. Inner Join- Produce records that have matching values in both tables.
  2. Left Join- Will return all the records from the left table and matching records from the right table.
  3. Right Join- Will return all the records from the right table and matching records from the left table.
  4. 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:

 
The left join clause will return all the records from the left table even if there are no matching records in the right table.

 

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;

 

Example:
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:

 
The FULL JOIN clause returns all matching records from both tables whether the other table matches or not. So, if there are records in "Customers" that do not have matches in "Orders", or if there are records in "Orders" that do not have matches in "Customers", those records will be listed as well.
A to Z Full Forms and Acronyms

Related Article