SQL Join With Microsoft SQL Server

4 minute read

What is SQL Joins?

A SQL join clause is used to combine records from two or more tables in a database based on common field between them. It creates a set of rows in a temporary table.

There are different types of joins available in SQL: • 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, Orders ord
 
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

Comments