Back to the list of all SQL Server 2000 / 2005 Interview Questions
|
|
Different types of JOINS in SQL Server
|
|
The following are the different types of Joins in SQL Server.
|
- Cross Join
- Inner Join or Join
- Left Outer Join or Left Join
- Right Outer Join or Right Join
- Full Outer Join or Full Join
|
Let us understand each type of Join with an example. Create 2 tables Company and Candidate. Use the script below to create these tables and populate them. CompanyId column in Candidate Table is a foreign key referencing CompanyId in Company Table.
|
|
SET
NOCOUNT ON
GO
USE
master
IF
EXISTS (SELECT
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME = 'Company')
DROP
TABLE Company
GO
CREATE
TABLE Company
(
CompanyId
TinyInt
Identity Primary Key,
CompanyName
Nvarchar(50) NULL
)
GO
INSERT
Company VALUES('DELL')
INSERT
Company VALUES('HP')
INSERT
Company VALUES('IBM')
INSERT
Company VALUES('Microsoft')
GO
IF
EXISTS (SELECT
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME = 'Candidate')
DROP
TABLE Candidate
GO
CREATE
TABLE Candidate
(
CandidateId
tinyint
identity primary key,
FullName
nvarchar(50)
NULL,
CompanyId
tinyint
REFERENCES Company(CompanyId)
)
GO
INSERT
Candidate VALUES('Ron',1)
INSERT
Candidate VALUES('Pete',2)
INSERT
Candidate VALUES('Steve',3)
INSERT
Candidate VALUES('Steve',NULL)
INSERT
Candidate VALUES('Ravi',1)
INSERT
Candidate VALUES('Raj',3)
INSERT
Candidate VALUES('Kiran',NULL)
GO
|
|
CROSS JOIN
|
A cross join produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. A query involving a CROSS JOIN for the Candidate and Company Table is shown below.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
CROSS
JOIN Company Comp
|
If we run the above query the output will be as shown in Results1 below.
|
Results1
|
|
Key Points to remember about CROSS JOIN
|
- A cross join produces the Cartesian product of the tables involved in the join.This mean every row in the Left Table is joined to every row in the Right Table. Candidate is LEFT Table and Company is RIGHT Table. In our example we have 28 total number of rows in the result set. 7 rows in the Candidate table multiplied by 4 rows in the Company Table.
- In real time scenarios we rarley use CROSS JOIN. Most often we use either INNER JOIN or LEFT OUTER JOIN.
- CROSS JOIN does not have an ON clause with a Join Condition. All the other JOINS use ON clause with a Join Condition.
- Using an ON clause on a CROSS JOIN would generate a syntax error.
|
|
INNER JOIN or JOIN
|
If you want to select all the rows from the LEFT table(In our example Candidate Table) that have a non null foreign key value(CompanyId in Candidate Table is the foreign key) then we use INNER JOIN. A query involving an INNER JOIN for the Candidate and Company Table is shown below.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
INNER
JOIN Company Comp
ON
Cand.CompanyId =
Comp.CompanyId
|
If we run the above query the output will be as shown in Results2 below. If you look at the out put, we only got 5 rows. We did not get the 2 rows which has NULL value in the CompanyId column. So an INNER JOIN would get all the rows from the LEFT Table that has non null foreign key value.
|
Results2
|
Instead of using INNER JOIN keyword we can just use JOIN keyword as shown below. JOIN or INNER JOIN means the same.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
JOIN
Company Comp
ON
Cand.CompanyId =
Comp.CompanyId
|
|
LEFT OUTER JOIN or LEFT JOIN
|
If you want to select all the rows from the LEFT table(In our example Candidate
Table) including the rows that have a null foreign key value(CompanyId in Candidate
Table is the foreign key) then we use LEFT OUTER JOIN. A query involving a LEFT
OUTER JOIN for the Candidate and Company Table is shown below.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
LEFT
OUTER JOIN Company
Comp
ON
Cand.CompanyId =
Comp.CompanyId
|
If we run the above query the output will be as shown in Results3 below. If you look at the out put, we now got all 7 rows(All the rows from the Candidate Table) including the row that has a null value for the CompanyId column in the Candidate Table.So, LEFT OUTER JOIN would get all the rows from the LEFT Table including the rows that has null foreign key value.
|
Results3
|
Instead of using LEFT OUTER JOIN keyword we can just use LEFT JOIN keyword as shown
below. LEFT OUTER JOIN or LEFT JOIN means the same.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
LEFT
JOIN Company Comp
ON
Cand.CompanyId =
Comp.CompanyId
|
|
RIGHT OUTER JOIN or RIGHT JOIN
|
If you want to select all the rows from the LEFT Table(In our example Candidate Table) that have non null foreign key values plus all the rows from the RIGHT table(In our example Company Table) including the rows that are not referenced in the LEFT Table, then we use RIGHT OUTER JOIN. A query involving a RIGHT OUTER JOIN for the Candidate and Company Table is shown below.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
RIGHT
OUTER JOIN Company
Comp
ON
Cand.CompanyId =
Comp.CompanyId
|
If we run the above query the output will be as shown in Results4 below. If you look at the out put, we now got 6 rows. All the rows from the Candidate Table that has non null foreign key value plus all the rows from the Company Table including the row that is not referenced in the Candidate Table.
|
Results4
|
Instead of using RIGHT OUTER JOIN keyword we can just use RIGHT JOIN keyword as
shown below. RIGHT OUTER JOIN or RIGHT JOIN means the same.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
RIGHT
JOIN Company Comp
ON
Cand.CompanyId =
Comp.CompanyId
|
|
FULL OUTER JOIN or FULL JOIN
|
If you want to select all the rows from the LEFT Table(In our example Candidate Table) plus all the rows from the RIGHT table(In our example Company Table), then we use FULL OUTER JOIN. A query involving a FULL OUTER JOIN for the Candidate and Company Table is shown below.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
FULL
OUTER JOIN Company
Comp
ON
Cand.CompanyId =
Comp.CompanyId
|
If we run the above query the output will be as shown in Results5 below. If you look at the out put, we now got 8 rows. All the rows from the Candidate Table and all the rows from the Company Table.
|
Results5
|
Instead of using FULL OUTER JOIN keyword we can just use FULL JOIN keyword as shown
below. FULL OUTER JOIN or FULL JOIN means the same.
|
|
SELECT
Cand.CandidateId,Cand.FullName,Cand.CompanyId,Comp.CompanyId,Comp.CompanyName
FROM
Candidate Cand
FULL
JOIN Company Comp
ON
Cand.CompanyId =
Comp.CompanyId
|
Back to the list of all SQL Server 2000 / 2005 Interview Questions
|