12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 1/10

10,244,631 members (65,242 online)

Sign in

home quick answers discussions features community help Search for articles, questions, tips

Articles » Database » Database » SQL Server

Article

Browse Code

Stats

Revisions (11)

Alternatives

Comments &Discussions (23)

About Article

Joins are useful for bringingdata together from differenttables based on theirdatabase relations. First wewill see how the joinoperates between tables.Then we will explore theOrder of Execution whenJoin and where conditionboth exists. Finally we willmove our exploration to theImportance o

Type Article

Licence CPOL

First Posted 6 Aug 2012

Views 53,993

Downloads 1,635

Bookmarked 67 times

SQL2005 SQL Windows

SQL-Server Beginner , +

Sign Up to vote

0Tweet 20

Understanding Table Joins using SQLBy Sivaraman Dhamodharan, 22 Jul 2013

Download CreateObject.zip - 334 B

Download instnwnd.zip - 262.3 KB

1. Introduction

Joins are useful for bringing data together from different tables based on their database relations. First wewill see how the join operates between tables. Then we will explore the Order of Execution when Join and

where condition both exists. Finally we will move our exploration to the Importance of the Join order.

2. Run the attached script

The downloaded script has the three table and table data for this article. You should also run the Northwnd script as

some example here uses the NorthWnd DB. Once you downloaded the script CreateObject.zip run the script in the

NorthWnd Db.

Below is the three tables created by the Script:

We are going to use these tables to perform the joins. These tables are just for demo purpose and So I donot have proper table relationship in terms of Primary and Foreign keys. OK, Let us move on.

3. Cartesian Product of Table

Usually join will be performed between two tables based on the key columns between two tables those together

constitutes the database table relationship. For Example DeptId in the employee table and DeptId in the Department

table make the relationship between these two tables.

The below one is the example Joining the tables without using the key columns. Here, TableA and TableBare clubbed together to form the whole result set based on Cartesian Product. The Cartesian product willtake a single record in the first table and attaches with all the records in the second table. Then takes theSecond records in the first table and attaches with all the records the second table and this continue till theend of the records on the first table.

4.48 (20 votes)

8Like

articles

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 2/10

Top News

Man throws away trove of

Bitcoin worth $7.5 million

Get the Insider News free eachmorning.

Related Videos

Related Articles

Visual Representation of SQLJoins

Difference between And clausealong with on and Where clausewhen used with left join in SQLServer

Creating SQL Joins in SimpleSteps

Understanding “Set based” and“Procedural” approaches in SQL

SQL Joins

Common TableExpressions(CTE) in SQL SERVER2008

A scripted SQL query generationframework with IDE: SQLpp(v1.4)

SQL Joins

SQL Query Optimization FAQPart 1 (With video explanation)

Joining Tables in SQL

Learn SQL to LINQ (VisualRepresentation)

SQL Tuning Tutorial -Understanding a DatabaseExecution Plan (1)

Quick Overview: TemporaryTables in SQL Server 2005

Concatenate Field Values in OneString Using CTE in SQL Server

Database Virtual Cursor

Get Tables List With ItsDependent Tables Names

Three Really Handy Tricks WithSQL Server Table Variables

Sort Database Tables By ForeignKeys

Database performanceoptimization part 2 (Indexmaintenance)

Inside Recursive CTEs

Related Research

4. Joining Two tables

When joining the two tables to avoid the bulk number of records that results as shown in the previous example, we

should chose a join column from both the tables. The example given below joins Table_A and Table_B based on the

Column called ID. Since the column in both the tables mapped based on the ID column, we will reduce huge records

that are logically not useful and not related the ID map.

Below is the Result of the Join:

Note that the Row Number 1 and Row number 5 are returned as the join result as they satisfies the mapping

condition A.Id = B.Id. In the query it is shown in the Red Box.

5. Joining multiple tables

The above example joint two tables. To join multiple tables, we should use the result of the previous join andpick a column from it, then pick a column in the new table then specify the join condition as the previousexample. This way we can join multiple numbers of tables. Consider whatever joint so far as the single table

and join it with the new one.

First Table_A is joins with Table_B, which is nothing but the previous example. Then the joint result of Aand B is considered as single table say AB. Then the AB is joint with the Table_C. This is shown in the below

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 3/10

In-The-Wild Testing: How toEnsure Your Apps Work in the

Real World

Protect Your Android App: WhyDevelopers Should Leverage

Secure Code SigningCertificates

picture:

6. Join Types

There are three type of join available based the way we join columns on two different tables.

1) Full outer Join

2) Inner Join

3) Left outer Join

4) Right outer Join

What we saw in the previous two examples are the inner joins. If we join the same table we called it as Self join and it

is special category do not get confuse it with the join types. Let us see an example for the join types in next coming

examples.

Before we go into those examples, remember that the result computed so for is considered as LEFT and thenew table coming to join the existing result is RIGHT. This is useful when we are joining multiple tables with

different type of joins.

7. Full Outer Join

A full outer join is somewhat different from the Cartesian product. Cartesian product will get all the possiblerow combination between the two joining tables. But, Full outer join takes all the matching columns by joincondition, all table rows from the left table that does not match the right side table, and, all tables rows inthe right that does not match the left. It applies null for unmatched row on the other end (Left side Tablecolumn or Right side table column) when doing so. The below example shows the full outer join betweenTable_A and Table_C

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 4/10

1. In the above picture the Blue Row the matching row on both the table.

2. Second row (Green First, red next) is the unmatched one. Row exists on the Left table and null substituted for all the

columns in the Right.

3. Third row (Red First, Green next) is also the unmatched one. Row exists on the Right side table, nullreturned for the left one

Look at the from clause,

The Table_A is taken first and joint with Table_C. Here, The result set computed so for always treated as

Left side of join and the new table going to be joint is treated as Right side of join.

8. Left Join

Left join makes sure to take all the rows on the left side of the table by placing the null entries for the table joining on

the right side when there is unmatched row on the right side.

In the above example, Id value of 2 in the Left table does not exist on the right side table Table_C.Id. But, we still got

the 2,BBB row from the Table_A by placing the null entries for the right side table. This is shown in Green and red

boxes above.

Also note that when SQL is processing, it takes the rows for the Table_A first (So Left) then joins it with theTable_C (Right side). It does not matter whether we provide A.Id = C.Id or C.Id = A.Id

9. Right Join

It is the reverse of the left join. It implies take all the rows on the right side of table by placing the null on the left table

for unmatched rows. Below is the example for it:

Blue Box : Matched rows.

Green : Row exits on the right side table Table_B and match (Based on Id column) not available on the left

Red : Null placement for the columns

10. Inner Join

In inner join only the matched rows are retrieved. Please refer the section four. Inner join returns same

result and hence one no need to worry about the placing a table on left or right.

11. Self Join

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 5/10

Joining the table with the same table is called the Self Join. To explain this let us go the table on theNorthwnd database (Attached with this article). Have a look at the columns in the employee table. TheEmployeeId column is the Primary key column and each row belongs to a single employee. The reports tocolumn refer some other row in the same table stating that referred row is the manager for the referring row.But the referred row (manager) is also an employee possible having a valid entry on its ReportsTo column.

So in the NorthWnd database this relationship achieves a hierarchical reporting structure.

Now have look at the below example:

Here, the row pointed by ReportTo column is Manager. So the table on the left hand side is employee andtable on the Right hand side is Manager. When the FirstName is referred on the left table of the joint result,

it is Employee name and the same FirstName on the right table of the join result is Manager name.

12. Execution Sequence

When the query involves the combination of the outer and inner join the execution sequence is important. If you

have only inner join the execution sequence is not much important as they are going to provide the same result. Well,

What I am taking about?

Let is Say you have a query, which has both inner join and outer join (Left or Right). Also let us assume you have where

clause that filters the records based on column that is not a joining id column. The question comes which operation is

performed first. We have two options:

1) Apply the where clause first because joining later will improve the performance then perform the join

2) Apply the Join then perform the where clause

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 6/10

The above two option returns same result when all the joins involved are inner joins. But the result maydiffer when we have at least one outer join. OK. SQL chose the second option because as per its operation

Sequence From clause is completed first, then applies the where clause.

Given below is an Example and the result:

How the Sequence differs is shown below:

Option 1:

Option 2:

So keep in mind the operation sequence as SQL first completes the join first then applies the where clausewhen the query has one or more outer joins.

13. Order of the Joins

Like the Operation sequence the Order of the join also important when you want mix the inner joins with outer (Left or

Right) joins. Again, if the entire join involved between the tables are inner joins the order of the join does not matter.

But it matters when we mix the inner and outer joins.

What is Order of the Join? If my query joins three tables like [X inner Y] Left Z, the order here is inner join performed

first, and then the left join.

OK. Let us go back to the NorthWnd Database again. The result you want to achieve is Get all the customers

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 7/10

names whether they have order or not. Also list the Qty of order placed by the customer if they actually has

some order.

Look at the Query and result below: [Outer Join then Inner Join]

From the above query, you can see the order of join as mentioned below:

1) A right join between Orders and Customers. SQL first queries the Orders table (As it appears first) and treats the

result as Left. Then it queries the Customers table next and treats the result set as Right. Finally from both the result

set Right join is performed that means SQL ensures you that it will not lose any rows on the Right side result set that

is it will not lose any rows from the Customers table. So you will get all customers including the two who don’t placed

any orders and since a matching records for those two rows are not available you will get null columns for the Orders.

Now the resulting join result is available for the next join and this join result is now treated as Left.

2) Now the above returned result (Left side) is joint with the Order Details table. SQL knows it already hasthe Left result set so it query the table Order Details to have the Right. Finally an Inner join is performedbetween Left and Right based on the Order Id. But note that we have two null entries for the orderedcolumn for which there are no corresponding customers in the Left side result. So the Inner join just skipsthose records. So we got a total of 2155 missing the two customers who does not place any orders, which is

not the result we need. Read the Underlines text at the top of this section.

Now look at the Query and Result below: [Inner Join then Outer Join]

Here, Inner join based on the OrderId between Orders and Order Details is performed first. This result (Left side) is

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 8/10

then Right joint against the Cutomers table [Right].

Now let us analyze how this is giving the result we want.

The inner join between Order and Order Details brings all the matching records based on the order id. Note that we

are not losing any order id here by null values. Then by keeping this already brought result on the left, Customers

table is queried and kept in the Right. Then the right join is performed between Customers and Left side result based

on the Customer Id. Now we get all the customers including the two for which we don’t have any matching records on

the Left side table.

So…

Keep in mind that join order is important when you mix the inner join with outer join.

14. Other way of achieving the same result

When I had a talk with one of my office friend (VaraPrasad), he told that the result you are expecting couldbe achieved without using the Right Join. How? That is the question I asked him. He told that Crystal reports

does it, I will show you. Good, now this section is added to this article based on what I got from him.

OK. Now let us see how this works and gives the expected result of not losing any customers. Note that the Rule

remains same, whatever computed so for is Left and the Joining table is on the Right.

1) SQL first queries the table Customers and keeps it as the result on the Left.

2) It reads the Open parenthesis, and queries the table Orders and keeps it Left again. Why? SQL Says “Boss, I know

that I am not going to join this table and Table I am going join is not ready yet. So I kept is Left side result.

3) Now the Order Details table is queried and kept as Right side of join as Left side is already available.

4) A join between Order and Order Details is performed based on the Order Id. The resultant records aretreated as right because the Customer table is already queried and kept in the Left. Now thee left joinbetween the Left and Right side of result set brings all customers as the join type left outer join.

Note: The scripts for creating the Demo tables and NorthWnd database is available as download.

License

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

About the Author

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 9/10

Sivaraman

DhamodharanSoftware Developer iSOFT

India

I am working as software engineer in iSOFT R&D. I have been come accross C++,MFC, .net

technologies. I do like playing video games, reading books.

Web: DotNet Programming Articles

Search this forum Go

Article Top

Comments and Discussions

You must Sign In to use this message board.

Profile popups Spacing Relaxed Noise Very High Layout Normal Per page 10 Update

First Prev Next

Member 10431234 27-Nov-13 20:33

Member 10353665 8-Nov-13 5:41

Bassam Abdul-Baki 24-Oct-13 5:28

heemanshubhalla 19-Sep-13 0:16

GregoryW 24-Jul-13 1:20

RajeshMathew 23-Jul-13 13:37

Brian A Stephens 8-Jul-13 2:16

Navas Khanj 24-Jan-13 8:50

Ravi Bhavnani 15-Aug-12 18:45

sirama2004 15-Aug-12 19:17

Last Visit: 31-Dec-99 18:00 Last Update: 4-Dec-13 2:31 Refresh 1 2 3 Next »

General News Suggestion Question Bug Answer Joke Rant Admin

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

Join

JOINS

Counting Bug

Joins in Sql

My vote of 4

My vote of 5

My vote of 5

JOIN TWO TALBE and Show to DataGRIDVIEW

Are you...

Re: Are you...

12/4/13 Understanding Table Joins using SQL - CodeProject

www.codeproject.com/Articles/435694/Understanding-Table-Joins-using-SQL 10/10

Permalink | Advertise | Privacy | Mobile Web03 | 2.7.131201.1 | Last Updated 22 Jul 2013

Article Copyright 2012 by Sivaraman DhamodharanEverything else Copyright © CodeProject, 1999-2013

Terms of Use

Layout: fixed | fluid