INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

JOIN Fundamentals by SemperFiDownUnda
Posted: 29 Jan 04 (Edited 29 Jan 04)

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.
INNER JOIN is in red
OUTER JOINs are in a tan/beige or olive green type color
CROSS JOIN is in blue

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.

Doctors                   Patients
ID FullName     MedCntrID ID FullName   DocID
1  Joe Manners   1        1  Jim Thick   4
2  Sue Tongs     1        2  Tom Small   2
3  Jeff Spine    1        3  Al Downs    4
4  Mary Rasch    2        4  Ann Hills   1
5  Tom Thumb     2        5  Tim Burrow  3
6  Norm Lobe     3        6  Jane Fern   5
                          7  Sam Broom   2
                          8  Gary Far    1
                          9  Bill Out    5
                          10 Dave Bell   4
                          11 Fred Overs  5
                          12 Greg Double 1
                          13 Bob Marks   9

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

JOINS take on the format of

      FROM
Left_Table
 JOIN_TYPE
Right_Table
        ON
Join_Condition



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

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM
Doctors D
 INNER JOIN
Patients P
    ON
D.ID = P.DocID

The query exectutes and grabs the first record in the Left_Table Doctors D

1  Joe Manners   1        

It then matches all records in the Right_Table Patients P based on the Join_Condition D.ID = P.DocID

4  Ann Hills   1
8  Gary Far    1
12 Greg Double 1

And produces this in the result set

DoctorName   PatientName
Joe Manners  Ann Hills
Joe Manners  Gary Far
Joe Manners  Greg Double

Then the next record in the Left_Table Doctors D is grabbed

2  Sue Tongs     1        

Then all matching records in the the Right_Table Patients P for this record based on the Join_Condition D.ID = P.DocID are found and the records are added to the result set.  The final result set for this query would look like this

DoctorName   PatientName
Joe Manners  Ann Hills
Joe Manners  Gary Far
Joe Manners  Greg Double
Sue Tongs    Tom Small
Sue Tongs    Sam Broom
Jeff Spine   Tim Burrow
Mary Rasch   Jim Thick
Mary Rasch   Al Downs
Mary Rasch   Dave Bell
Tom Thumb    Jane Fern
Tom Thumb    Bill Out
Tom Thumb    Fred Overs

Note that Norm Lobe does not appear in the result set because no Patients have him listed as a doctor.  Also Bob Marks does not appear in the result set because his doctor is not in the Doctors table


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 Join_Condition 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 Left_Table and tries to match records based on the Join_Condition in the Right_Table. If record(s) are found in the Right_Table then they are match just as they would be in the INNER JOIN.  If no match is found in the Right_Tablethen only 1 row is added to the result set for the record in the Left_Table and the columns that come from the Right_Table have the value of NULL

So a query of


SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM
Doctors D
  LEFT OUTER JOIN
Patients P
    ON
D.ID = P.DocID


has a result set of

DoctorName   PatientName
Joe Manners  Ann Hills
Joe Manners  Gary Far
Joe Manners  Greg Double
Sue Tongs    Tom Small
Sue Tongs    Sam Broom
Jeff Spine   Tim Burrow
Mary Rasch   Jim Thick
Mary Rasch   Al Downs
Mary Rasch   Dave Bell
Tom Thumb    Jane Fern
Tom Thumb    Bill Out
Tom Thumb    Fred Overs
Norm Lobe    NULL

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 Left_Table will end up in the result set.

RIGHT OUTER JOIN - This JOIN is just like the LEFT OUTER JOIN except the Right_Table is the table that will have every one of its records in the result set and if no record is found in the
Left_Table then its columns in the result set will be NULL

So a query of


SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM
Doctors D
 RIGHT OUTER JOIN
Patients P
    ON
D.ID = P.DocID


has a result set of
DoctorName   PatientName
Joe Manners  Ann Hills
Joe Manners  Gary Far
Joe Manners  Greg Double
Sue Tongs    Tom Small
Sue Tongs    Sam Broom
Jeff Spine   Tim Burrow
Mary Rasch   Jim Thick
Mary Rasch   Al Downs
Mary Rasch   Dave Bell
Tom Thumb    Jane Fern
Tom Thumb    Bill Out
Tom Thumb    Fred Overs
NULL         Bob Marks

FULL OUTER JOIN - This JOIN is a combination of both.  All records from both Left_Table and Right_Table are in the result set and matched when they can be on the Join_Condition when no record is found in the opposit table NULL values are used for the columns.
So a query of


SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM
Doctors D
  FULL OUTER JOIN
Patients P
    ON
D.ID = P.DocID


has a result set of
Norm Lobe    NULL
DoctorName   PatientName
Joe Manners  Ann Hills
Joe Manners  Gary Far
Joe Manners  Greg Double
Sue Tongs    Tom Small
Sue Tongs    Sam Broom
Jeff Spine   Tim Burrow
Mary Rasch   Jim Thick
Mary Rasch   Al Downs
Mary Rasch   Dave Bell
Tom Thumb    Jane Fern
Tom Thumb    Bill Out
Tom Thumb    Fred Overs
NULL         Bob Marks




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.

CROSS JOIN This JOIN has a slightly different format in that it does not have a ON clause with a Join_Condition.  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 Left_Table is joined to every row in the Right_Table.  For our tables with 6 doctors and 13 patients we would get a result set of 6x13 or 78 records.  The query would look like


SELECT D.FullName AS DoctorName, P.FullName AS PatientName
  FROM
Doctors D
 CROSS JOIN
Patients P


The result set would look roughly like this

Joe Manners  Jim Thick
Joe Manners  Tom Small
Joe Manners  Al Downs
Joe Manners  Ann Hills
Joe Manners  Tim Burrow
.
.
.
Norm Lobe    Fred Overs
Norm Lobe    Greg Double
Norm Lobe    Bob Marks


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



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 Left_Table 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

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


*NOTE 1 INNER JOIN because you only want patients that have a doctor that is on your register
*NOTE 1 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.


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.


Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close