Click here to Skip to main content
11,641,211 members (62,283 online)
Click here to Skip to main content

Differences Between Various Joins in MS SQL Server

, 18 Aug 2014 CPOL 7.5K 93 12
Rate this:
Please Sign up or sign in to vote.
Here, we will see the basic differences or similarities between various joins of MS SQL Server.

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 .

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:

Limitations

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DiponRoy
Bangladesh Bangladesh
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralTaken a huge 'part' ;) Pin
Neophyte Nitol19-Apr-15 6:40
memberNeophyte Nitol19-Apr-15 6:40 
GeneralRe: Taken a huge 'part' ;) Pin
DiponRoy19-Apr-15 7:56
memberDiponRoy19-Apr-15 7:56 
GeneralRe: Taken a huge 'part' ;) Pin
Neophyte Nitol23-Apr-15 0:23
memberNeophyte Nitol23-Apr-15 0:23 
GeneralMessage Removed Pin
Neophyte Nitol23-Apr-15 0:23
memberNeophyte Nitol23-Apr-15 0:23 
GeneralRe: Taken a huge 'part' ;) Pin
CHill6023-Apr-15 0:27
protectorCHill6023-Apr-15 0:27 
GeneralMy vote of 5 Pin
Sibeesh KV19-Sep-14 19:44
professionalSibeesh KV19-Sep-14 19:44 
GeneralRe: My vote of 5 Pin
diponsust20-Sep-14 1:20
memberdiponsust20-Sep-14 1:20 
GeneralRe: My vote of 5 Pin
Sibeesh KV20-Sep-14 3:56
professionalSibeesh KV20-Sep-14 3:56 
GeneralMy vote of 3 Pin
coded00720-Aug-14 2:53
professionalcoded00720-Aug-14 2:53 
GeneralRe: My vote of 3 Pin
diponsust20-Aug-14 3:57
memberdiponsust20-Aug-14 3:57 
GeneralMy vote of 1 Pin
BufferUnderrun20-Aug-14 2:29
memberBufferUnderrun20-Aug-14 2:29 
GeneralRe: My vote of 1 Pin
diponsust20-Aug-14 4:03
memberdiponsust20-Aug-14 4:03 
Suggestionspell check nitpicking Pin
afbach19-Aug-14 7:32
memberafbach19-Aug-14 7:32 
GeneralRe: spell check nitpicking Pin
diponsust19-Aug-14 8:58
memberdiponsust19-Aug-14 8:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150731.1 | Last Updated 18 Aug 2014
Article Copyright 2014 by DiponRoy
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid