Different Types of SQL Joins

In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table.

I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows:

•  INNER JOIN
•  LEFT OUTER JOIN
•  RIGHT OUTER JOIN
•  FULL OUTER JOIN
•  CROSS JOIN
•  SELF JOIN
•  LEFT JOIN
•  RIGHT JOIN

Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO

INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:SELECT *
FROM Table1 A
INNER JOIN Table2 B
ON A.ID = B.ID
GO

LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

CROSS JOIN
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query forSELECT * FROM Table1 A
CROSS JOIN Table2 B
GO

Column Name	Data Type	Allow Nulls
EmpId	Int (set Identity=true)	No
EmpName	varchar(50)	Yes
ManagerId	Int	Yes

EmpId	EmpName	ManagerId
1	Abdul	0
2	Faisal	1
3	Sourabh	1
4	Deepak	2
5	Manish	2
6	Sandeep	5

SELECT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

EmpId	EmpName
1	Abdul
2	Faisal
5	Manish

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (Customers), even if there are no matches in the right table (tblOrders).

1. Customers
2. tblorders

So here it will display all the records of the customers table along with the matching “CustId” in table Orders. If any “Cust Id” does not match in the “tblOrders” It will be null for that particular part.

Now see the query:

select cust.Amount,cust.CustomerName,cust.CustId,ordr.CustId,ordr .OrderName  from dbo.Customer as cust Left join
dbo.tblOrder as ordr on cust.CustId =ordr .CustId

Now run the query; it will look like:

See here from the Customer table all the 11 records are displaying along with the customer that has the order in order table. But the customers who don’t have an order in the orders table i.e. the “Cust Id” is not matching are displaying here NULL Values. See I marked with red.

That means that in the Orders table we don’t have the Cust id 6,7,9,10,11.

Now for the Second Requirements

“Display the whole Order Name from the order details table along with the Customer Name that has the order in the Orders Table”.
For this we have to do RIGHT OUTER JOIN.

 RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the Right table (tblOrders), even if there are no matches in the Left table (Customers).
It is the opposite of Left Outer join.

1. Customers
2. tblorders

So here it will display all the records of the Orders table along with the matching “CustId” in the Customer Tables. If any “Cust Id” does not match in the “Customers” table then it will show null for that particular part.

Now see the query:

select cust.Amount,cust.CustomerName,cust.CustId,ordr.OrderId,ordr .OrderName  from dbo.Customer as cust Right join dbo.tblOrder as ordr on cust.CustId =ordr .CustId

See here I used the “Right Join” keyword.

Run the query; it will look like:

See here all the records of the orders table are displaying (marked with red) along with all CustomerNames with matching “CustId” in the “Orders” Table.

See here the OrderId 7 and 8 are displaying Null (marked with blue) values in the Customer Tables. That means there are no matching “CustId” in the “Orders” table

 

******************************IMPORTANT POINTS*****************************************************************

LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.