Joins

Join facilitates the retrieval of information from multiple tables. In Sql server we have following 6 types of Joins:

1. INNER JOIN
2. LEFT OUTER JOIN
3. RIGHT OUTER JOIN
4. FULL OUTER JOIN
5. CROSS JOIN
6. SELF JOIN

To demo these features let us first create the Customers and Orders table as depicted in the below image by using the following script:

CREATE DATABASE SqlHintsJoinDemo GO USE SqlHintsJoinDemo GO --Create Customers Table and Insert records CREATE TABLE Customers ( CustomerId INT, Name VARCHAR(50) ) GO INSERT INTO Customers(CustomerId, Name) VALUES(1,'Shree') INSERT INTO Customers(CustomerId, Name) VALUES(2,'Kalpana') INSERT INTO Customers(CustomerId, Name) VALUES(3,'Basavaraj') GO   --Create Orders Table and Insert records into it CREATE TABLE Orders (OrderId INT, CustomerId INT, OrderDate DateTime) GO INSERT INTO Orders(OrderId, CustomerId, OrderDate) VALUES(100,1,Getdate()-1)   INSERT INTO Orders VALUES(200,4,Getdate()) INSERT INTO Orders VALUES(300,3,Getdate()+1) GO

1. INNER JOIN in Sql Server

Inner Join returns only the matching rows in both the tables (i.e. returns only those rows for which the join condition satisfies).

Demo 1: As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the Inner Join on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 only.

SELECT * FROM  Customers C         INNER JOIN Orders O             ON O.CustomerId = C.CustomerId

RESULT:

Demo 2: Below Inner Join query demonstrates how to get name of all the  Customer who have at-least one order in the Orders table.

SELECT C.Name FROM  Customers C         INNER JOIN Orders O             ON O.CustomerId = C.CustomerId

RESULT:
Name
——————-
Basavaraj
Shree

2. Left OUTER JOIN in Sql Server

Left Outer Join/Left Join returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right table doesn’t have the matching record then for such records right table column will have NULL value in the result.

Demo 1: As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the Left join on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 and for CustomerId 2 the Order Table columns will have NULL value in the result.

SELECT * FROM  Customers C         LEFT OUTER JOIN Orders O             ON O.CustomerId = C.CustomerId

RESULT:

Demo 2: Below query demonstrates how to get the name of the Customer who don’t have Orders using LEFT OUTER JOIN.

SELECT C.CustomerId, C.Name FROM  Customers C         LEFT OUTER JOIN Orders O             ON O.CustomerId = C.CustomerId WHERE O.OrderId IS NULL

RESULT:
CustomerId Name
———– ————————————————–
2 Kalpana

3. RIGHT OUTER JOIN in Sql Server

Right Outer Join/Right Join returns all the rows from the RIGHT table and the corresponding matching rows from the left table. If left table doesn’t have the matching record then for such records left table column will have NULL value in the result.

Demo 1: As per the data in our demo tables, only for the order with OrderId 200 we don’t have it’s corresponding customer info with CustomerId 4 in the Customers table. And for the other two orders, the corresponding customer info is present in the Customers Table. So for the orders with CustomerId 1 and 3 will have customer details and for the order with CustomerId 4, the Customers table columns will have NULL value in the result.

SELECT * FROM  Customers C         RIGHT OUTER JOIN Orders O             ON O.CustomerId = C.CustomerId

RESULT:

Demo 2: Below query demonstrates how to get the Orders with a CustomerId, for which we don’t have a mapping any record in the Customers Table:

SELECT O.* FROM  Customers C         RIGHT OUTER JOIN Orders O             ON O.CustomerId = C.CustomerId WHERE C.CustomerId IS NULL

RESULT:
OrderId CustomerId OrderDate
———– ———– ———————–
200 4 2014-01-31 23:48:32.853

4. FULL OUTER JOIN in Sql Server

It returns all the rows from both the tables, if there is no matching row in either of the sides then it displays NULL values in the result for that table columns in such rows.

Full Outer Join = Left Outer Join + Right Outer Join

Demo 1: As per the data in our Demo tables the Customer with CustomerId 2 doesn’t have order in the Orders table. So in the result of FULL Outer join between Customers and Orders table on the CustomerId column will have NULL values for the Orders table columns for the Customer with CustomerId 2.

And for the Order with OrderId 200 having CustomerId 4 doesn’t have a matching record in the customer table with CustomerId 4. So in the result of FULL Outer join between Customers and Orders table on the CustomerId column will have NULL values for the Customers table columns for the Order with OrderId 200.

SELECT *     FROM Customers C             FULL OUTER JOIN Orders O                 ON O.CustomerId = C.CustomerId

RESULT:

Demo 2: Below query demonstrates how to get the list of all the Customers without Orders and also the Orders which doesn’t have corresponding customer in the Customers Table.

SELECT *     FROM Customers C             FULL OUTER JOIN Orders O                 ON O.CustomerId = C.CustomerId WHERE C.CustomerId IS NULL OR O.OrderId IS NULL

RESULT:

5. CROSS JOIN in Sql Server

Cross join is also referred to as Cartesian Product. For every row in the LEFT Table of the CROSS JOIN all the rows from the RIGHT table are returned and Vice-Versa (i.e.result will have the Cartesian product of the rows from join tables).

No.of Rows in the Result of CRoss Join = (No. of Rows in LEFT Table) * (No. of Rows in RIGHT Table)

SELECT * FROM Customers C         CROSS JOIN Orders O

RESULT:

6. SELF JOIN in Sql Server

If a Table is joined to itself using one of the join types explained above, then such a type of join is called SELF JOIN.

To demo this join let us create an Employee table with data as depicted in the below image by the following script:

CREATE TABLE Employee (EmployeeId INT, Name NVARCHAR(50), ManagerId INT) GO INSERT INTO Employee VALUES(1,'Shree',1) INSERT INTO Employee VALUES(2,'Kalpana',1) INSERT INTO Employee VALUES(3,'Basavaraj',2) INSERT INTO Employee VALUES(4,'Monty',2) GO

Demo 1: Now if we need to get the name of the Employee and his Manager name for each employee in the Employee Table. Then we have to Join Employee Table to itself as Employee and his Manager data is present in this table only as shown in the below query:

SELECT E.EmployeeId,        E.Name 'Employee Name', M.Name 'Manager Name' FROM dbo.Employee E         INNER JOIN Employee M             ON M.EmployeeId = E.ManagerId

RESULT: