Differences Between Various Joins in MS SQL Server

• Download source - 53.7 KB

Introduction

When working with SQL joins, we can chose among from various choices, depending on the requirement. Well, each join sequence may differ from one another, and serves a special purpose. But few among these join queries differ in syntax /performance but they produce the same thing. To avoid confusion, here we will see the basic differences and similarities between various joins of MS SQL Server.

Background

Let’s say we have two tables:

CREATE Table Student
(    
    Id BIGINT,
    Name VARCHAR(100)
);

CREATE Table StudentAge
(    
    Student_Id BIGINT,  --foreign key from Student tables Id column
    Name VARCHAR(100),
    Age INT
);

Yes, there is no real foreign key between them, but let’s say we populated these tables with relational data like:

INSERT INTO Student
VALUES
(1, 'Dipon'),
(2, 'Dip'),
(3, 'Dipa'),    -- no age
(4, 'Dipika'),  -- no age
(5, 'Shamim'),  -- has age more than once
(6, 'Shatu'),   -- same person twice
(6, 'Shatu')

INSERT INTO StudentAge
VALUES
(1, 'Dipon', 10),
(2, 'Dip', 20),
(5, 'Shamim', 40),  -- same person twice age
(5, 'Shamim', 50),
(6, 'Shatu', 50),   -- same age to more than one people
(7, 'Jamal', 50),   -- no Student
(8, 'Kamal', 60)    -- no Student

As we can see, considering these two tables, some of the rows have relational data and for few there is none.

Let’s Start Joining

Gentlemen like to do hand shake
Sometimes, they shake hand if they only know each other.
Sometimes, they do the same even if they don’t know each other.
Sometimes, one may feel embarrassed, if he does find anyone to shake hands with.

Shakes hand - join made between rows
Know each other - has any data relation
Embarrassment revealed - no row found to join, NULL

Regular Joins

Shake hand if they know each other, no embarrassment revealed.

Join: JOIN

/*Join: JOIN*/
/*does: Shakes hand if they know each other, no embarrassment revealed*/
SELECT *
    FROM Student
    JOIN StudentAge ON Student.Id = StudentAge.Student_Id;

Join: INNER JOIN

/*Join: INNER JOIN*/
/*does: as JOIN*/
SELECT *
    FROM Student
    INNER JOIN StudentAge ON Student.Id = StudentAge.Student_Id;

Join: WHERE

/*Join: WHERE*/
/*does: as JOIN*/
SELECT *
    FROM Student, StudentAge
    WHERE Student.Id = StudentAge.Student_Id;

CROSS APPLY

/*CROSS APPLY*/
/*does: as JOIN*/
SELECT *
    FROM Student
    CROSS APPLY(
        SELECT *
            FROM StudentAge
            WHERE Student.Id = StudentAge.Student_Id
    )StudentAges

They all produced the same thing:

http://www.sqlfiddle.com/#!3/517ad/1

Left Joins

Each person shakes hands if they know each other, embarrassment revealed for the left man.

Join: LEFT JOIN

/*Join: LEFT JOIN*/
/*does: Each person shakes hand if they know each other, embarrassment revealed for the left man*/
SELECT *
    FROM Student
    LEFT JOIN StudentAge ON Student.Id = StudentAge.Student_Id;

Join: LEFT OUTER JOIN

/*Join: LEFT OUTER JOIN*/
/*does: as LEFT JOIN*/
SELECT *
    FROM Student
    LEFT OUTER JOIN StudentAge ON Student.Id = StudentAge.Student_Id;

OUTER APPLY

/*OUTER APPLY*/
/*does: as LEFT JOIN*/    
SELECT *
    FROM Student
    OUTER APPLY(
        SELECT *
            FROM StudentAge
            WHERE Student.Id = StudentAge.Student_Id
    )StudentAges

They all produced the same thing.

http://www.sqlfiddle.com/#!3/517ad/2

Right Joins

Each person shakes hand if they know each other, embarrassment revealed for the right man.

Join: RIGHT JOIN

/*Join: RIGHT JOIN*/
/*does: Each person shakes hand if they know each other, embarrassment revealed for the right man*/
SELECT *
    FROM Student
    RIGHT JOIN StudentAge ON Student.Id = StudentAge.Student_Id;

Join: RIGHT OUTER JOIN

/*Join: RIGHT OUTER JOIN*/
/*does: as RIGHT JOIN*/
SELECT *
    FROM Student
    RIGHT OUTER JOIN StudentAge ON Student.Id = StudentAge.Student_Id;

OUTER APPLY

/*OUTER APPLY*/
/*does: as LEFT JOIN, but presented as RIGHT JOIN using (SELECT Students.*, StudentAge.**/        
SELECT Students.*, StudentAge.*
    FROM StudentAge
    OUTER APPLY(
        SELECT *
            FROM Student
            WHERE StudentAge.Student_Id = Student.Id
    )Students

They all produced the same thing.

http://www.sqlfiddle.com/#!3/517ad/3

Full Joins

Each person shakes hand if they know each other, embarrassment revealed for both men.

Join: FULL JOIN

/*Join: FULL JOIN*/
/*does: Each person shakes hand if they know each other, embarrassment revealed for the both man*/
SELECT *
    FROM Student
    FULL JOIN StudentAge ON Student.Id = StudentAge.Student_Id;

Join: FULL OUTER JOIN

/*Join: FULL OUTER JOIN*/
/*does: as FULL JOIN*/
SELECT *
    FROM Student
    FULL OUTER JOIN StudentAge ON Student.Id = StudentAge.Student_Id;

They all produced the same thing.

http://www.sqlfiddle.com/#!3/517ad/4

Cross Joins

Each one shakes hand with everyone.

Join: CROSS JOIN

/*Join: CROSS JOIN*/
/*does: Each one shakes hand with every one*/
SELECT *
    FROM Student
    CROSS JOIN StudentAge;

Join: TABLE

/*Join: TABLE*/
/*does: as CROSS JOIN*/
SELECT *
    FROM Student, StudentAge;

CROSS APPLY

/*CROSS APPLY*/
/*does: as CROSS JOIN*/
SELECT *
    FROM Student
    CROSS APPLY(
        SELECT *
            FROM StudentAge
    )StudentAges

They all produced the same thing.

http://www.sqlfiddle.com/#!3/517ad/5

Other Joins!!!

When working with joins, we may face a situation where we want to do few more things like:

• LEFT JOIN EXCLUDING INNER JOIN
• RIGHT JOIN EXCLUDING INNER JOIN
• OUTER JOIN EXCLUDING INNER JOIN
• Etc. or some real basics

Check it out at http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins, which is a great article by C.L. Moffatt.

Cross/Outer Apply !!!

The only intention to use Cross/ Outer Apply is performance issue. It performs faster than the normal joins. For more, take some overview from:

• http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
• http://technet.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx

Limitations

Yes, there could be something which I misunderstood or presented. So if you find anything, just let me know.