Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

JOIN Fundamentals

T-SQL Hints and Tips

JOIN Fundamentals

by SemperFiDownUnda Posted Jan 28, 2004 (Edited Jan 29, 2004)

Very often we need to pull data from 2 or more sources (tables or views) and combined them into 1 logical unit.

There is often a bit of confusion on when and how to use them.

I will first describe each JOIN type and how it works with the 2 tables involved. The major JOIN types are color coded to make it easier to see where that join type ends.
[color #FF0000]INNER JOIN is in red[/color]
[color #808000]OUTER JOINs are in a tan/beige or olive green type color[/color]
[color #000080]CROSS JOIN is in blue[/color]

I'll then go into some hints and

pitfalls when JOINing more than 2 tables.

Lets get started - For this FAQ I'll be using an example of a fictious health care system. We have 2 simple tables for the initial discussion.

[color #00A000]

Code:

Doctors

[/color][color #A00000]

Code:

Patients

[/color]
[color #00A000]

Code:

ID FullName     MedCntrID

[/color][color #A00000]

Code:

ID FullName   DocID

[/color]
[color #00A000]

Code:

1  Joe Manners   1

[/color][color #A00000]

Code:

1  Jim Thick   4

[/color]
[color #00A000]

Code:

2  Sue Tongs     1

[/color][color #A00000]

Code:

2  Tom Small   2

[/color]
[color #00A000]

Code:

3  Jeff Spine    1

[/color][color #A00000]

Code:

3  Al Downs    4

[/color]
[color #00A000]

Code:

4  Mary Rasch    2

[/color][color #A00000]

Code:

4  Ann Hills   1

[/color]
[color #00A000]

Code:

5  Tom Thumb     2

[/color][color #A00000]

Code:

5  Tim Burrow  3

[/color]
[color #00A000]

Code:

6  Norm Lobe     3

[/color][color #A00000]

Code:

6  Jane Fern   5

[/color]
[color #00A000]

Code:

[/color][color #A00000]

Code:

7  Sam Broom   2

[/color]
[color #00A000]

Code:

[/color][color #A00000]

Code:

8  Gary Far    1

[/color]
[color #00A000]

Code:

[/color][color #A00000]

Code:

9  Bill Out    5

[/color]
[color #00A000]

Code:

[/color][color #A00000]

Code:

10 Dave Bell   4

[/color]
[color #00A000]

Code:

[/color][color #A00000]

Code:

11 Fred Overs  5

[/color]
[color #00A000]

Code:

[/color][color #A00000]

Code:

12 Greg Double 1

[/color]
[color #00A000]

Code:

[/color][color #A00000]

Code:

13 Bob Marks   9

[/color]

Now that we have our 2 tables lets see how we can join them

JOINS take on the format of

Code:

      FROM

[color #00A000]

Code:

Left_Table

[/color]

Code:

 JOIN_TYPE

[color #A00000]

Code:

Right_Table

[/color]

Code:

ON

[color #FF00FF]

Code:

Join_Condition

[/color]

Code:

[color #FF0000]INNER JOIN - This JOIN takes every record in the Left_Table and looks for 1 or more matches in the Right_Table based on the Join_Condition. If 1 is found the record is added to the result set. If more then one matching record is found in the Right_Table then there are multiple records added to the result set.

Lets look at this in action
[color #CC0000]

Code:

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM

[color #00A000]

Code:

Doctors D

[/color]

Code:

 INNER JOIN

[color #A00000]

Code:

Patients P

[/color]

Code:

ON

[color #FF00FF]

Code:

D.ID = P.DocID

[/color]

Code:

[/color]The query exectutes and grabs the first record in the [color #00A000]Left_Table

Code:

Doctors D

[/color]

[color #00A000][color #FF00FF]

Code:

[/color]

Code:

  Joe Manners   1

[/color]

It then matches all records in the [color #A00000]Right_Table

Code:

Patients P

[/color] based on the [color #FF00FF]Join_Condition

Code:

D.ID = P.DocID

[/color]

[color #A00000]

Code:

4  Ann Hills

[color #FF00FF]

Code:

[/color][/color]
[color #A00000]

Code:

8  Gary Far

[color #FF00FF]

Code:

[/color][/color]
[color #A00000]

Code:

12 Greg Double

[color #FF00FF]

Code:

[/color][/color]

And produces this in the result set

[color #00A000]

Code:

 DoctorName

[/color][color #A00000]

Code:

PatientName

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Ann Hills

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Gary Far

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Greg Double

[/color]

Then the next record in the [color #00A000]Left_Table

Code:

Doctors D

[/color] is grabbed

[color #00A000]

Code:

2  Sue Tongs     1

[/color]

Then all matching records in the the [color #A00000]Right_Table

Code:

Patients P

[/color] for this record based on the [color #FF00FF]Join_Condition

Code:

D.ID = P.DocID

[/color] are found and the records are added to the result set. The final result set for this query would look like this

[color #00A000]

Code:

 DoctorName

[/color][color #A00000]

Code:

PatientName

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Ann Hills

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Gary Far

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Greg Double

[/color]
[color #00A000]

Code:

 Sue Tongs

[/color][color #A00000]

Code:

Tom Small

[/color]
[color #00A000]

Code:

 Sue Tongs

[/color][color #A00000]

Code:

Sam Broom

[/color]
[color #00A000]

Code:

 Jeff Spine

[/color][color #A00000]

Code:

Tim Burrow

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Jim Thick

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Al Downs

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Dave Bell

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Jane Fern

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Bill Out

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Fred Overs

[/color]

Note that[color #00A000]

Code:

 Norm Lobe

[/color] does not appear in the result set because no [color #A00000]Patients[/color] have him listed as a doctor. Also [color #A00000]

Code:

Bob Marks

[/color] does not appear in the result set because his doctor is not in the [color #00A000]Doctors[/color] table[/color]

[color #808000]OUTER JOIN - This JOIN takes on 3 variations. All 3 have a similar function. These JOINs are designed to bring 2 tables together but include data even if there the [color #FF00FF]Join_Condition [/color] is does not find a matching record(s). What it does is fill in the tables columns with NULLs. Lets take the the different types and talk about them.

LEFT OUTER JOIN - This JOIN, is a bit like the INNER JOIN. It takes the [color #00A000]

Code:

Left_Table

[/color] and tries to match records based on the [color #FF00FF]

Code:

Join_Condition

[/color] in the [color #A00000]

Code:

Right_Table

[/color]. If record(s) are found in the [color #A00000]

Code:

Right_Table

[/color] then they are match just as they would be in the INNER JOIN. If no match is found in the [color #A00000]

Code:

Right_Table

[/color]then only 1 row is added to the result set for the record in the [color #00A000]

Code:

Left_Table

[/color] and the columns that come from the [color #A00000]

Code:

Right_Table

[/color] have the value of NULL

So a query of

[color #606000]

Code:

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM

[color #00A000]

Code:

Doctors D

[/color]

Code:

  LEFT OUTER JOIN

[color #A00000]

Code:

Patients P

[/color]

Code:

ON

[color #FF00FF]

Code:

D.ID = P.DocID

[/color]

Code:

[/color]

has a result set of

[color #00A000]

Code:

 DoctorName

[/color][color #A00000]

Code:

PatientName

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Ann Hills

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Gary Far

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Greg Double

[/color]
[color #00A000]

Code:

 Sue Tongs

[/color][color #A00000]

Code:

Tom Small

[/color]
[color #00A000]

Code:

 Sue Tongs

[/color][color #A00000]

Code:

Sam Broom

[/color]
[color #00A000]

Code:

 Jeff Spine

[/color][color #A00000]

Code:

Tim Burrow

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Jim Thick

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Al Downs

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Dave Bell

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Jane Fern

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Bill Out

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Fred Overs

[/color]
[color #00A000]

Code:

Norm Lobe

[/color][color #A00000]

Code:

NULL

[/color]

Note this is the same result set as the INNER JOIN but includes a the bolded record because the rule is that ever record in the [color #00A000]

Code:

Left_Table

[/color] will end up in the result set.

RIGHT OUTER JOIN - This JOIN is just like the LEFT OUTER JOIN except the [color #A00000]

Code:

Right_Table

[/color] is the table that will have every one of its records in the result set and if no record is found in the
[color #00A000]

Code:

Left_Table

[/color] then its columns in the result set will be NULL

So a query of

[color #606000]

Code:

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM

[color #00A000]

Code:

Doctors D

[/color]

Code:

 RIGHT OUTER JOIN

[color #A00000]

Code:

Patients P

[/color]

Code:

ON

[color #FF00FF]

Code:

D.ID = P.DocID

[/color]

Code:

[/color]

has a result set of
[color #00A000]

Code:

 DoctorName

[/color][color #A00000]

Code:

PatientName

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Ann Hills

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Gary Far

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Greg Double

[/color]
[color #00A000]

Code:

 Sue Tongs

[/color][color #A00000]

Code:

Tom Small

[/color]
[color #00A000]

Code:

 Sue Tongs

[/color][color #A00000]

Code:

Sam Broom

[/color]
[color #00A000]

Code:

 Jeff Spine

[/color][color #A00000]

Code:

Tim Burrow

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Jim Thick

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Al Downs

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Dave Bell

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Jane Fern

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Bill Out

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Fred Overs

[/color]
[color #00A000]

Code:

NULL

[/color][color #A00000]

Code:

Bob Marks

[/color]

FULL OUTER JOIN - This JOIN is a combination of both. All records from both [color #00A000]

Code:

Left_Table

[/color] and [color #A00000]

Code:

Right_Table

[/color] are in the result set and matched when they can be on the [color #FF00FF]Join_Condition[/color] when no record is found in the opposit table NULL values are used for the columns.
So a query of

[color #606000]

Code:

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM

[color #00A000]

Code:

Doctors D

[/color]

Code:

  FULL OUTER JOIN

[color #A00000]

Code:

Patients P

[/color]

Code:

ON

[color #FF00FF]

Code:

D.ID = P.DocID

[/color]

Code:

[/color]

has a result set of
[color #00A000]

Code:

Norm Lobe

[/color][color #A00000]

Code:

NULL

[/color]
[color #00A000]

Code:

 DoctorName

[/color][color #A00000]

Code:

PatientName

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Ann Hills

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Gary Far

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Greg Double

[/color]
[color #00A000]

Code:

 Sue Tongs

[/color][color #A00000]

Code:

Tom Small

[/color]
[color #00A000]

Code:

 Sue Tongs

[/color][color #A00000]

Code:

Sam Broom

[/color]
[color #00A000]

Code:

 Jeff Spine

[/color][color #A00000]

Code:

Tim Burrow

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Jim Thick

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Al Downs

[/color]
[color #00A000]

Code:

 Mary Rasch

[/color][color #A00000]

Code:

Dave Bell

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Jane Fern

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Bill Out

[/color]
[color #00A000]

Code:

 Tom Thumb

[/color][color #A00000]

Code:

Fred Overs

[/color]
[color #00A000]

Code:

NULL

[/color][color #A00000]

Code:

Bob Marks

[/color]

[/color]

Now there is one more Join type in T-SQL. This is not frequently used and I've personally come up with 1 use for it and that is filling up a table with dummy data to get a rough idea of performance. r937 has shown me a few other times you can use these and links to those Threads are at the bottom of this section.

[color #000080]CROSS JOIN This JOIN has a slightly different format in that it does not have a ON clause with a [color #FF00FF]Join_Condition[/color]. This is because of the nature of the CROSS JOIN it doesn't need a join condition. What it does is perform a cartesian product of the tables involved in the join. This mean every row in the [color #00A000]

Code:

Left_Table

[/color] is joined to every row in the [color #A00000]

Code:

Right_Table

[/color]. For our tables with 6 [color #00A000]doctors[/color] and 13 [color #A00000]patients[/color] we would get a result set of 6x13 or 78 records. The query would look like

[color #000060]

Code:

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM

[color #00A000]

Code:

Doctors D

[/color]

Code:

 CROSS JOIN

[color #A00000]

Code:

Patients P

[/color]

Code:

[/color]

The result set would look roughly like this

[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Jim Thick

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Tom Small

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Al Downs

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Ann Hills

[/color]
[color #00A000]

Code:

 Joe Manners

[/color][color #A00000]

Code:

Tim Burrow

[/color]
.
.
.
[color #00A000]

Code:

 Norm Lobe

[/color][color #A00000]

Code:

Fred Overs

[/color]
[color #00A000]

Code:

 Norm Lobe

[/color][color #A00000]

Code:

Greg Double[/color]
[color #00A000]

Code:

 Norm Lobe

[/color][color #A00000]

Code:

Bob Marks[/color]

following threads are some great examples of using cross joins by r937, thanks for enlightening me.

Thread183-755853 -- triple cross-join of the integers 0 through 9 to create the numbers which are used to generate a range of dates

Thread436-755873 -- cross join a single row of one table to unrelated rows of another

Thread701-728879 -- cross join two tables to get all possible combinations, then outer join to find the ones that are missing

[/color]

Now there are the JOINs and how they work. What about when you want to join more then 2 tables?
Really you can only join 2 tables together. You just can have multiple sets of 2 tables and 1 single table may be joined to more then 1 other table, heck you can even join a table back to itself.

Some rules to follow to make things easier.
1) Use a LEFT OUTER JOIN over a RIGHT OUTER JOIN when ever possible. It will make things easier as you can always think of the [color #00A000]

Code:

Left_Table

[/color] as the base table.

2) Start from you base table and work outwards towards the auxillary tables. Say you wanted to get a list of Patients with that have doctors on your register and pull the information about the Medical Center the doctor works at if it is avialable then you would do it like this

Code:

SELECT ....
  FROM Patients P
 INNER JOIN Doctors D  [i]*NOTE 1[/i]
    ON P.DocID = D.ID
  LEFT OUTER JOIN MedicalCenters MC [i]*NOTE 2[/i]
    ON D.MedCntrID = MC.ID
[code]

[i]*NOTE 1[/i] INNER JOIN because you only want patients that have a doctor that is on your register
[i]*NOTE 1[/i] Left OUTER JOIN because you want the information about the medical center if you have it but if you don't you still want the record to be included.  If  you made this a INNER JOIN then if a Doctor's medical center was not on record then the doctor would not be included in the result set meaing that any of their patients would not be on the result set since Patients and Doctors is done with an INNER JOIN too.

3) You can have multiple conditions for the ON clause just like you can in a WHERE clause.  Make sure you know the difference between a JOIN condition and a WHERE clause.  Though they look similiar in task they can execute slightly different giving unexpected results if you are not careful.

4) Use Alias in tables names they can make reading your queries much easiers

5) Format your queries for easy reading.  A query doesn't run any faster if its all on 1 line or spread over 40 lines.  As you can see from my formating style it is very easy to see what joins to what and the conditions they use which means less of a chance you made a error and will get unexpected results.

6) JOIN conditions are prime canidates for indexs.  On small tables it doesn't matter but large tables will show great performance benifits from haveing the columns indexed.

I hope this little FAQ helps you understand JOINs better.


[i]Thanks to SQLSister and r937 for going over it and finding my mistakes and to r937 for some different, very useful, examples of cross joins. [/i]

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Accept Learn more…

Back

Top