Joining Tables in SQL - CodeProject
Content
Joining Tables in SQL - CodeProject
1 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
11,873,912 members (61,075 online)
Sign in
Search for articles, questions, tips
home
articles
quick answers
discussions
features
community
help
Articles » Database » Database » SQL
Joining Tables in SQL
Pierre Kellerman, 3 Apr 2013
CPOL
Rate this:
4.84 (27 votes)
Explaining different ways to join tables
Introduction
This document will try and explain the ways that joining two tables work. To help explain this I created two temp tables with specific rows
in them to proof the point. Find the SQL to create the sample Temp tables in Appendix A. We have table TEMP_A with four rows in it the
ID's of this four rows in unique and numbered 1,2,3,4 respectively. Then we also have table TEMP_B with five rows in it. It has rows 1,
2,3,3,5 in it. Note that row 1 and 2 from Table A have one reference each in table B.
Row 3 have two reverences in table B row 4 have no reverences in table B at all, and there is an Orphan row in table B (row 5) that have no
parent row in table A.
Also note that the reserved words inner and outer is optional. left outer join and left join mean exactly the same thing.
OK now on to the fun stuff.
Normal Join (Or Inner Join)
Joining (or inner joining) the two table on the ID fields you will get all rows in the intersection of the two sets, meaning where they both
have the same value.
Using the data sample created in Appendix A we will get the following result:
Hide Copy Code
select *
from TEMP_A
INNER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
2 of 6
3
Tbl A Row 3
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
3
Tbl B Row 3b
Note that row 3 in table a is duplicated once for each corresponding row in table B.
Left Join (Or Left Outer Join)
The Left join will return the Intersection of the two tables and in addition it will also return the rows from the left table that do not have
corresponding rows in the right table. What is left and what is right. Well the Left table is the first table specified and the right is the
second table specified. Or the Right table is the table after the Join statement. The Left side is the rest of the data SQL is working with.
Using the data sample created in Appendix A we will get the following result.
Hide Copy Code
select *
from TEMP_A
LEFT OUTER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
4
Tbl A Row 4
NULL
NULL
Note that Row 4 from table A is now included but since there is not corresponding row in table B all the fields from table B contain
NULL's.
Right Join (Or Right Outer Join)
The Right Join Is very much like the left join but it return rows from the Right Table that have no corresponding rows in the Left table.
Using the data sample created in Appendix A we will get the following result.
Hide Copy Code
select *
from TEMP_A
RIGHT OUTER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
3 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
NULL
NULL
5
Tbl B Row 5
This set also contains 5 rows. But the last row this time contains data from table B and all data from table A is NULL.
Full Join (Or Full Outer Join)
Well this is like a left and a Right join Combined. It will return the intersection of the two tables, and all the rows from table A not having
corresponding rows in B and all the rows from B not having corresponding rows in A.
Using the data sample created in Appendix A we will get the following result:
Hide Copy Code
select *
from TEMP_A
FULL OUTER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
4
Tbl A Row 4
NULL
NULL
NULL
NULL
5
Tbl B Row 5
Cross Join
Well a Cross join is not really a join you do not specify the fields to join on you just specify the name of the tables. It will return every row
from table A matched up with every row in table B so the end result will have lots of rows.
Using the data sample created in Appendix A we will get the following result.
Hide Copy Code
select *
from TEMP_A, TEMP_B
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
1
Tbl A Row 1
2
Tbl B Row 2
1
Tbl A Row 1
3
Tbl B Row 3a
1
Tbl A Row 1
3
Tbl B Row 3b
1
Tbl A Row 1
5
Tbl B Row 5
2
Tbl A Row 2
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
2
Tbl A Row 2
3
Tbl B Row 3a
2
Tbl A Row 2
3
Tbl B Row 3b
2
Tbl A Row 2
5
Tbl B Row 5
3
Tbl A Row 3
1
Tbl B Row 1
3
Tbl A Row 3
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
3
Tbl A Row 3
5
Tbl B Row 5
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
4 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
4
Tbl A Row 4
1
Tbl B Row 1
4
Tbl A Row 4
2
Tbl B Row 2
4
Tbl A Row 4
3
Tbl B Row 3a
4
Tbl A Row 4
3
Tbl B Row 3b
4
Tbl A Row 4
5
Tbl B Row 5
Why not using a unique key is bad
During this explanation I used table A with unique values in the TBL_ID field. It is not desired that you use table where the joining key is
not unique in at least one of the two tables. If this is the case you will find that the non-unique key will perform cross joins. Let's add a row
to Table A to show this.
Hide Copy Code
insert into TEMP_A values (3, 'Tbl A Row 3 dup')
Now select a normal Join as before and watch the results.
Hide Copy Code
select *
from TEMP_A
INNER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
3
Tbl A Row 3 dup
3
Tbl B Row 3a
3
Tbl A Row 3 dup
3
Tbl B Row 3b
See how we now retuned 6 rows.
Using Join to select orphans
So how do I get all the rows in one table that do not have corresponding rows from the other table? Simple with a left or right join and a
where removing the unwanted rows.
Using the sample data:
Hide Copy Code
select *
from TEMP_A
LEFT OUTER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
where TEMP_B.Tbl_ID is null
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
4
Tbl A Row 4
NULL
NULL
Appendix A (Sample Data)
Hide Copy Code
-- Create the first temp table
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
5 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
create table TEMP_A (
Tbl_ID int not null,
Tbl_Data varchar(50) not null
)
-- Insert sample data in to first temp table
insert into TEMP_A values (1, 'Tbl A Row 1')
insert into TEMP_A values (2, 'Tbl A Row 2')
insert into TEMP_A values (3, 'Tbl A Row 3')
insert into TEMP_A values (4, 'Tbl A Row 4')
-- Create the second temp table
create table TEMP_B (
Tbl_ID int not null,
Tbl_Data varchar(50) not null
)
-- Inset sample data into the second
insert into TEMP_B values (1, 'Tbl B
insert into TEMP_B values (2, 'Tbl B
insert into TEMP_B values (3, 'Tbl B
insert into TEMP_B values (3, 'Tbl B
insert into TEMP_B values (5, 'Tbl B
temp table
Row 1')
Row 2')
Row 3a')
Row 3b')
Row 5')
License
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Share
EMAIL
TWITTER
About the Author
Pierre Kellerman
Team Leader Real People
South Africa
No Biography provided
You may also be interested in...
SQL Joins
Optimize SharePoint Storage with
BLOB Externalization
Visual Representation of SQL Joins
Is SQL Server killing your
application’s performance?
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
6 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
SQL Joins
SAPrefs - Netscape-like Preferences
Dialog
Comments and Discussions
You must Sign In to use this message board.
Go
Search Comments
Profile popups
Spacing Relaxed
Layout Normal
Per page 25
Update
First Prev Next
My vote of 5
csharpbd
My vote of 5
Amir Farid
Awesome
blitzkrieged
My vote of 3
prahalad.gaggar
10-Apr-13 3:28
My vote of 5
Ken Kazinski
10-Apr-13 2:18
My vote of 5
SRSHINDE
9-Apr-13 19:01
My vote of 5
fox6367
8-Apr-13 0:23
My vote of 5
Sherdz
7-Apr-13 22:38
My vote of 2
Selvin
5-Apr-13 1:09
Re: My vote of 2
16-Mar-14 21:17
5-Oct-13 21:48
3-Sep-13 3:06
Patrick Harris
9-Apr-13 17:32
Selvin
10-Apr-13 0:11
My vote of 5
cpaganin
4-Apr-13 23:08
My vote of 2
Member 9963480
My vote of 5
bluesathish
3-Apr-13 19:35
My vote of 5
TechnoGeek001
3-Apr-13 19:25
My vote of 5
Nikhil_S
3-Apr-13 19:01
Re: My vote of 2
Last Visit: 31-Dec-99 19:00
General
News
4-Apr-13 9:51
Last Update: 5-Nov-15 4:18
Suggestion
Question
Refresh
Bug
Answer
Joke
Praise
Rant
1
Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151103.1 | Last Updated 3 Apr 2013
Layout: fixed | fluid
Article Copyright 2013 by Pierre Kellerman
Everything else Copyright © CodeProject, 1999-2015
6/11/2015 12:56 AM
1 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
11,873,912 members (61,075 online)
Sign in
Search for articles, questions, tips
home
articles
quick answers
discussions
features
community
help
Articles » Database » Database » SQL
Joining Tables in SQL
Pierre Kellerman, 3 Apr 2013
CPOL
Rate this:
4.84 (27 votes)
Explaining different ways to join tables
Introduction
This document will try and explain the ways that joining two tables work. To help explain this I created two temp tables with specific rows
in them to proof the point. Find the SQL to create the sample Temp tables in Appendix A. We have table TEMP_A with four rows in it the
ID's of this four rows in unique and numbered 1,2,3,4 respectively. Then we also have table TEMP_B with five rows in it. It has rows 1,
2,3,3,5 in it. Note that row 1 and 2 from Table A have one reference each in table B.
Row 3 have two reverences in table B row 4 have no reverences in table B at all, and there is an Orphan row in table B (row 5) that have no
parent row in table A.
Also note that the reserved words inner and outer is optional. left outer join and left join mean exactly the same thing.
OK now on to the fun stuff.
Normal Join (Or Inner Join)
Joining (or inner joining) the two table on the ID fields you will get all rows in the intersection of the two sets, meaning where they both
have the same value.
Using the data sample created in Appendix A we will get the following result:
Hide Copy Code
select *
from TEMP_A
INNER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
2 of 6
3
Tbl A Row 3
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
3
Tbl B Row 3b
Note that row 3 in table a is duplicated once for each corresponding row in table B.
Left Join (Or Left Outer Join)
The Left join will return the Intersection of the two tables and in addition it will also return the rows from the left table that do not have
corresponding rows in the right table. What is left and what is right. Well the Left table is the first table specified and the right is the
second table specified. Or the Right table is the table after the Join statement. The Left side is the rest of the data SQL is working with.
Using the data sample created in Appendix A we will get the following result.
Hide Copy Code
select *
from TEMP_A
LEFT OUTER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
4
Tbl A Row 4
NULL
NULL
Note that Row 4 from table A is now included but since there is not corresponding row in table B all the fields from table B contain
NULL's.
Right Join (Or Right Outer Join)
The Right Join Is very much like the left join but it return rows from the Right Table that have no corresponding rows in the Left table.
Using the data sample created in Appendix A we will get the following result.
Hide Copy Code
select *
from TEMP_A
RIGHT OUTER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
3 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
NULL
NULL
5
Tbl B Row 5
This set also contains 5 rows. But the last row this time contains data from table B and all data from table A is NULL.
Full Join (Or Full Outer Join)
Well this is like a left and a Right join Combined. It will return the intersection of the two tables, and all the rows from table A not having
corresponding rows in B and all the rows from B not having corresponding rows in A.
Using the data sample created in Appendix A we will get the following result:
Hide Copy Code
select *
from TEMP_A
FULL OUTER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
4
Tbl A Row 4
NULL
NULL
NULL
NULL
5
Tbl B Row 5
Cross Join
Well a Cross join is not really a join you do not specify the fields to join on you just specify the name of the tables. It will return every row
from table A matched up with every row in table B so the end result will have lots of rows.
Using the data sample created in Appendix A we will get the following result.
Hide Copy Code
select *
from TEMP_A, TEMP_B
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
1
Tbl A Row 1
2
Tbl B Row 2
1
Tbl A Row 1
3
Tbl B Row 3a
1
Tbl A Row 1
3
Tbl B Row 3b
1
Tbl A Row 1
5
Tbl B Row 5
2
Tbl A Row 2
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
2
Tbl A Row 2
3
Tbl B Row 3a
2
Tbl A Row 2
3
Tbl B Row 3b
2
Tbl A Row 2
5
Tbl B Row 5
3
Tbl A Row 3
1
Tbl B Row 1
3
Tbl A Row 3
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
3
Tbl A Row 3
5
Tbl B Row 5
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
4 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
4
Tbl A Row 4
1
Tbl B Row 1
4
Tbl A Row 4
2
Tbl B Row 2
4
Tbl A Row 4
3
Tbl B Row 3a
4
Tbl A Row 4
3
Tbl B Row 3b
4
Tbl A Row 4
5
Tbl B Row 5
Why not using a unique key is bad
During this explanation I used table A with unique values in the TBL_ID field. It is not desired that you use table where the joining key is
not unique in at least one of the two tables. If this is the case you will find that the non-unique key will perform cross joins. Let's add a row
to Table A to show this.
Hide Copy Code
insert into TEMP_A values (3, 'Tbl A Row 3 dup')
Now select a normal Join as before and watch the results.
Hide Copy Code
select *
from TEMP_A
INNER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
1
Tbl A Row 1
1
Tbl B Row 1
2
Tbl A Row 2
2
Tbl B Row 2
3
Tbl A Row 3
3
Tbl B Row 3a
3
Tbl A Row 3
3
Tbl B Row 3b
3
Tbl A Row 3 dup
3
Tbl B Row 3a
3
Tbl A Row 3 dup
3
Tbl B Row 3b
See how we now retuned 6 rows.
Using Join to select orphans
So how do I get all the rows in one table that do not have corresponding rows from the other table? Simple with a left or right join and a
where removing the unwanted rows.
Using the sample data:
Hide Copy Code
select *
from TEMP_A
LEFT OUTER JOIN TEMP_B
ON TEMP_A.Tbl_ID = TEMP_B.Tbl_ID
where TEMP_B.Tbl_ID is null
Tbl_ID
Tbl_Data
Tbl_ID
Tbl_Data
4
Tbl A Row 4
NULL
NULL
Appendix A (Sample Data)
Hide Copy Code
-- Create the first temp table
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
5 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
create table TEMP_A (
Tbl_ID int not null,
Tbl_Data varchar(50) not null
)
-- Insert sample data in to first temp table
insert into TEMP_A values (1, 'Tbl A Row 1')
insert into TEMP_A values (2, 'Tbl A Row 2')
insert into TEMP_A values (3, 'Tbl A Row 3')
insert into TEMP_A values (4, 'Tbl A Row 4')
-- Create the second temp table
create table TEMP_B (
Tbl_ID int not null,
Tbl_Data varchar(50) not null
)
-- Inset sample data into the second
insert into TEMP_B values (1, 'Tbl B
insert into TEMP_B values (2, 'Tbl B
insert into TEMP_B values (3, 'Tbl B
insert into TEMP_B values (3, 'Tbl B
insert into TEMP_B values (5, 'Tbl B
temp table
Row 1')
Row 2')
Row 3a')
Row 3b')
Row 5')
License
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Share
About the Author
Pierre Kellerman
Team Leader Real People
South Africa
No Biography provided
You may also be interested in...
SQL Joins
Optimize SharePoint Storage with
BLOB Externalization
Visual Representation of SQL Joins
Is SQL Server killing your
application’s performance?
6/11/2015 12:56 AM
Joining Tables in SQL - CodeProject
6 of 6
http://www.codeproject.com/Articles/571933/Joining-Tables-in-SQL
SQL Joins
SAPrefs - Netscape-like Preferences
Dialog
Comments and Discussions
You must Sign In to use this message board.
Go
Search Comments
Profile popups
Spacing Relaxed
Layout Normal
Per page 25
Update
First Prev Next
My vote of 5
csharpbd
My vote of 5
Amir Farid
Awesome
blitzkrieged
My vote of 3
prahalad.gaggar
10-Apr-13 3:28
My vote of 5
Ken Kazinski
10-Apr-13 2:18
My vote of 5
SRSHINDE
9-Apr-13 19:01
My vote of 5
fox6367
8-Apr-13 0:23
My vote of 5
Sherdz
7-Apr-13 22:38
My vote of 2
Selvin
5-Apr-13 1:09
Re: My vote of 2
16-Mar-14 21:17
5-Oct-13 21:48
3-Sep-13 3:06
Patrick Harris
9-Apr-13 17:32
Selvin
10-Apr-13 0:11
My vote of 5
cpaganin
4-Apr-13 23:08
My vote of 2
Member 9963480
My vote of 5
bluesathish
3-Apr-13 19:35
My vote of 5
TechnoGeek001
3-Apr-13 19:25
My vote of 5
Nikhil_S
3-Apr-13 19:01
Re: My vote of 2
Last Visit: 31-Dec-99 19:00
General
News
4-Apr-13 9:51
Last Update: 5-Nov-15 4:18
Suggestion
Question
Refresh
Bug
Answer
Joke
Praise
Rant
1
Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151103.1 | Last Updated 3 Apr 2013
Layout: fixed | fluid
Article Copyright 2013 by Pierre Kellerman
Everything else Copyright © CodeProject, 1999-2015
6/11/2015 12:56 AM
