SQL Join With Microsoft SQL Server
What is SQL Joins?
In SQL, a join is a mechanism to combine data from two or more tables based on related columns between them. A join allows you to retrieve data from multiple tables as a single result set.
There are several types of joins in SQL, including:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- SELF JOIN
- CARTESIAN JOIN/CROSS JOIN
To understand JOINs, let’s consider the following two tables Customers and Orders is as follows
Customers
Orders
INNER JOIN
INNER JOIN returns match data between tables. It returns rows when there is at least one match in both the tables.
Syntax:
SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
INNER JOIN Orders ord
ON Cust.Id = ord.CustomerId;
Output:
| ID | Name | Amount | Date |
|---|---|---|---|
| 2 | Hamidur Rahman | 4000.00 | 2014-01-10 |
| 2 | Hamidur Rahman | 5000.00 | 2014-01-10 |
| 3 | Jamilur Rahman | 3000.00 | 2014-02-13 |
| 4 | Hasan Sarwar | 600.00 | 2014-03-22 |
LEFT JOIN/LEFT OUTER JOIN
Left Outer Join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
LEFT JOIN Orders ord
ON Cust.Id = ord.CustomerId;
Output:
| Id | Name | Amount | Date |
|---|---|---|---|
| 1 | Mahedee Hasan | NULL | NULL |
| 2 | Hamidur Rahman | 4000.00 | 2014-01-10 |
| 2 | Hamidur Rahman | 5000.00 | 2014-01-10 |
| 3 | Jamilur Rahman | 3000.00 | 2014-02-13 |
| 4 | Hasan Sarwar | 600.00 | 2014-03-22 |
| 5 | Kamal Hossain | NULL | NULL |
| 6 | Imran Khan | NULL | NULL |
| 7 | Abu Sayem | NULL | NULL |
RIGHT JOIN/RIGHT OUTER JOIN
Right outer join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
Syntax:
SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
RIGHT JOIN Orders ord
ON Cust.Id = ord.CustomerId;
Output:
| Id | Name | Amount | Date |
|---|---|---|---|
| 2 | Hamidur Rahman | 4000.00 | 2014-01-10 |
| 2 | Hamidur Rahman | 5000.00 | 2014-01-10 |
| 3 | Jamilur Rahman | 3000.00 | 2014-02-13 |
| 4 | Hasan Sarwar | 600.00 | 2014-03-22 |
| NULL | NULL | 800.00 | 2014-03-22 |
FULL JOIN /FULL OUTER JOIN
Full outer join is combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match
Syntax:
SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
FULL JOIN Orders ord
ON Cust.Id = ord.CustomerId;
Output:
| Id | Name | Amount | Date |
|---|---|---|---|
| 1 | Mahedee Hasan | NULL | NULL |
| 2 | Hamidur Rahman | 4000.00 | 2014-01-10 |
| 2 | Hamidur Rahman | 5000.00 | 2014-01-10 |
| 3 | Jamilur Rahman | 3000.00 | 2014-02-13 |
| 4 | Hasan Sarwar | 600.00 | 2014-03-22 |
| 5 | Kamal Hossain | NULL | NULL |
| 6 | Imran Khan | NULL | NULL |
| 7 | Abu Sayem | NULL | NULL |
| NULL | NULL | 800.00 | 2014-03-22 |
SELF JOIN
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Syntax:
SELECT A.Id, A.Name, B.Name AS Introducer
FROM Customers A, Customers B
WHERE A.IntroducerId = B.Id
Output:
| Id | Name | Introducer |
|---|---|---|
| 1 | Mahedee Hasan | Hamidur Rahman |
| 2 | Hamidur Rahman | Mahedee Hasan |
| 3 | Jamilur Rahman | Hamidur Rahman |
| 4 | Hasan Sarwar | Jamilur Rahman |
| 5 | Kamal Hossain | Hasan Sarwar |
| 6 | Imran Khan | Kamal Hossain |
| 7 | Abu Sayem | Imran Khan |
CARTESIAN JOIN/CROSS JOIN
The SQL cross join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause uses in the second table
Syntax:
SELECT cust.Id, cust.Name, ord.Amount, ord.[Date]
FROM Customers cust CROSS JOIN Orders ord
Output:
| Id | Name | Amount | Date |
|---|---|---|---|
| 1 | Mahedee Hasan | 4000.00 | 2014-01-10 |
| 2 | Hamidur Rahman | 4000.00 | 2014-01-10 |
| 3 | Jamilur Rahman | 4000.00 | 2014-01-10 |
| 4 | Hasan Sarwar | 4000.00 | 2014-01-10 |
| 5 | Kamal Hossain | 4000.00 | 2014-01-10 |
| 6 | Imran Khan | 4000.00 | 2014-01-10 |
| 7 | Abu Sayem | 4000.00 | 2014-01-10 |
| 1 | Mahedee Hasan | 5000.00 | 2014-01-10 |
| 2 | Hamidur Rahman | 5000.00 | 2014-01-10 |
| 3 | Jamilur Rahman | 5000.00 | 2014-01-10 |
| 4 | Hasan Sarwar | 5000.00 | 2014-01-10 |
| 5 | Kamal Hossain | 5000.00 | 2014-01-10 |
| 6 | Imran Khan | 5000.00 | 2014-01-10 |
| 7 | Abu Sayem | 5000.00 | 2014-01-10 |
| 1 | Mahedee Hasan | 3000.00 | 2014-02-13 |
| 2 | Hamidur Rahman | 3000.00 | 2014-02-13 |
| 3 | Jamilur Rahman | 3000.00 | 2014-02-13 |
| 4 | Hasan Sarwar | 3000.00 | 2014-02-13 |
| 5 | Kamal Hossain | 3000.00 | 2014-02-13 |
| 6 | Imran Khan | 3000.00 | 2014-02-13 |
| 7 | Abu Sayem | 3000.00 | 2014-02-13 |
| 1 | Mahedee Hasan | 600.00 | 2014-03-22 |
| 2 | Hamidur Rahman | 600.00 | 2014-03-22 |
| 3 | Jamilur Rahman | 600.00 | 2014-03-22 |
| 4 | Hasan Sarwar | 600.00 | 2014-03-22 |
| 5 | Kamal Hossain | 600.00 | 2014-03-22 |
| 6 | Imran Khan | 600.00 | 2014-03-22 |
| 7 | Abu Sayem | 600.00 | 2014-03-22 |
| 1 | Mahedee Hasan | 800.00 | 2014-03-22 |
| 2 | Hamidur Rahman | 800.00 | 2014-03-22 |
| 3 | Jamilur Rahman | 800.00 | 2014-03-22 |
| 4 | Hasan Sarwar | 800.00 | 2014-03-22 |
| 5 | Kamal Hossain | 800.00 | 2014-03-22 |
| 6 | Imran Khan | 800.00 | 2014-03-22 |
| 7 | Abu Sayem | 800.00 | 2014-03-22 |
Additional Information Related JOIN
Left Outer Join Where Null Left outer join where null returns all the rows from the left table in conjunction without the matching rows from the right table.
Syntax
SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
LEFT JOIN Orders ord
ON Cust.Id = ord.CustomerId
where ord.CustomerId is null
Output
| Id | Name | Amount | Date |
|---|---|---|---|
| 1 | Mahedee Hasan | NULL | NULL |
| 5 | Kamal Hossain | NULL | NULL |
| 6 | Imran Khan | NULL | NULL |
| 7 | Abu Sayem | NULL | NULL |
Right Outer Join Where Null
Right outer join where null returns all the rows from the right table in conjunction without the matching rows from the left table.
Syntax:
SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
RIGHT JOIN Orders ord
ON Cust.Id = ord.CustomerId
where Cust.Id is null
Output
| Id | Name | Amount | Date |
|---|---|---|---|
| NULL | NULL | 800.00 | 2014-03-22 |
Full Outer Join Where Null
Full outer join is combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match
Syntax
SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
FULL OUTER JOIN Orders ord
ON Cust.Id = ord.CustomerId
WHERE Cust.Id is null
OR ord.CustomerId is null
Output
| Id | Name | Amount | Date |
|---|---|---|---|
| 1 | Mahedee Hasan | NULL | NULL |
| 5 | Kamal Hossain | NULL | NULL |
| 6 | Imran Khan | NULL | NULL |
| 7 | Abu Sayem | NULL | NULL |
| NULL | NULL | 800.00 | 2014-03-22 |