Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Refresher on Joins

By Jambu Krishnamurthy,

In this article we will look at JOINS. We will primarily focus towards beginners, but this may be a refresher for the experienced. We will see how each type of JOIN works. Specifically we will discuss these: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN , SELF JOIN and then wind up with CROSS JOINS.

Note there are so many other terminologies used, like equi join, non-equi join, inner join etc, but there are only the 6 types of joins as mentioned above.

Let's start by creating the tables/data required to understand JOINS.

Step - 1

Use the following script to create the table and data. You can just cut and paste this code into Query Analyzer and execute it.

drop table t1,t2,t3

create table t1(f1 int)
create table t2(f1 int)
create table t3(f1 int)
--===========================
set nocount on
declare @j int
set @j = 1
while @j <= 10
begin
	insert into t1 values(@j)
	set @j = @j + 1
end
--===========================
set nocount on
declare @k int
set @k = 6
while @k <= 15
begin
	insert into t2 values(@k)
	set @k = @k + 1
end
--===========================
insert into t3 values(6)
insert into t3 values(2)
insert into t3 values(3)
insert into t3 values(12)
insert into t3 values(13)
--===========================

You will now have data like this in the three tables. Please note that the empty spaces in rows are only to show a pictorial mapping. There are only 10 records in t1, 10 records in t2 and 5 records in t3 (no empty rows or NULL values in any field)

t1	t2	t3
f1	f1	f1
--	--	--
1
2		2
3		3
4
5
6	6	6
7	7
8	8
9	9
10	10
	11
	12	12
	13	13
	14
	15

Step - 2

Try these queries

select * from
t1 JOIN t2
on t1.f1 = t2.f1

select * from
t1 INNER JOIN t2
on t1.f1 = t2.f1

select * from
	t1,t2
where
	t1.f1 = t2.f1

All the three queries above are essentially the same. The last one is referred to as the equi-join. What if you want all rows in t1 that are not in t2 (non-equi joins ?). Will this query work?

select * from
	t1,t2
where
	t1.f1 <> t2.f1

It did not quite do what you expected, huh?

Here is one way to achieve this:

select * from t1
where t1.f1 not in (select t2.f1 from t2)

Step - 3 - LEFT OUTER JOIN

Now suppose you want all the records from t1, whether they have a corresponding entry in t2 or not. Why will we ever want to do this? A simple situation could be that, you want a list of all the departments (t1) whether there are employees or not in that department (t2). For this example assume t1.f2 holds department ids, and t2.f1 as department ids fk'd into t1.f1. Here is the query

select t1.f1, t2.f1
from t1 LEFT OUTER JOIN t2
on (t1.f1 = t2.f1)

This is exactly similar in functionality to the above.

select t1.f1, t2.f1
from t1 LEFT JOIN t2
on (t1.f1 = t2.f1)

t1.f1   t2.f1
=====   =====
1       NULL
2       NULL
3       NULL
4       NULL
5       NULL
6       6
7       7
8       8
9       9
10      10

Step - 4 - RIGHT OUTER JOIN

Ok, now the reverse, assume we want all the employees, whether or not they have been assigned to any departments. Both the queries below are similar again

select t1.f1, t2.f1
from t1 RIGHT OUTER JOIN t2
on (t1.f1 = t2.f1)

select t1.f1, t2.f1
from t1 RIGHT JOIN t2
on (t1.f1 = t2.f1)

t1.f1 t2.f1
===== =====
6     6
7     7
8     8
9     9
10    10
NULL  11
NULL  12
NULL  13
NULL  14
NULL  15

Step - 5 - FULL OUTER JOIN

Ah, what if you now want a list of all rows in t1 (whether or not there are rows in t2) and all the rows in t2 (whether or not there are rows in t1)

select t1.f1, t2.f1 from t1 FULL OUTER JOIN t2 on (t1.f1 = t2.f1) order by t1.f1 select t1.f1, t2.f1 from t1 FULL JOIN t2 on (t1.f1 = t2.f1) order by t1.f1 t1.f1 t2.f1 ===== ===== NULL 11 NULL 12 NULL 13 NULL 14 NULL 15 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL 6 6 7 7 8 8 9 9 10 10

Step - 6

JOINS make much sense between two tables, but can obviously be extended to more than two tables. However, just to demonstrate, as to how complicated/confusing they can become, here is a FULL OUTER JOIN example between t1, t2 and t3.

select a.f1, b.f1, c.f1
from t1 a
FULL OUTER JOIN t2 b on (a.f1 = b.f1)
FULL OUTER JOIN t3 c on (a.f1 = c.f1)

t1.f1 t2.f1 t3.f1
===== ===== =====
6     6     6
7     7     NULL
8     8     NULL
9     9     NULL
10    10    NULL
NULL  11    NULL
NULL  12    NULL
NULL  13    NULL
NULL  14    NULL
NULL  15    NULL
5     NULL  NULL
4     NULL  NULL
3     NULL  3
2     NULL  2
1     NULL  NULL
NULL  NULL  13
NULL  NULL  12

Observe the query and output carefully...and then see if you can get the following output, which you will agree makes more sense.

t1.f1 t2.f1 t3.f1
===== ===== =====
6     6     6
7     7     NULL
8     8     NULL
9     9     NULL
10    10    NULL
NULL  11    NULL
NULL  12    12
NULL  13    13
NULL  14    NULL
NULL  15    NULL
5     NULL  NULL
4     NULL  NULL
3     NULL  3
2     NULL  2
1     NULL  NULL

That's it for OUTER JOINS for now.

Step - 7 - SELF JOINS

When the same table is used in a query with two different aliases, such a join is referred to as a self-join. Let us see this case with an example. Create the following table and data.

create table jk_Workers(Worker int, Manager int)

insert into jk_Workers values(111,NULL)
insert into jk_Workers values(222,111)
insert into jk_Workers values(333,222)
insert into jk_Workers values(444,222)
insert into jk_Workers values(555,222)
insert into jk_Workers values(666,111)
insert into jk_Workers values(777,111)
insert into jk_Workers values(888,333)
insert into jk_Workers values(999,222)

All the workers with their respective managers are stored in this table. And if we wish to list out the managers and the workers working under them, we could use a query similar to the following.

select b.Manager,b.Worker
from jk_Workers a, jk_Workers b
where a.Worker = b.Manager
or b.manager is null and a.Manager is null
order by b.Manager

Manager Worker
NULL    111
111     222
111     666
111     777
222     999
222     333
222     444
222     555
333     888

Step - 8 - CROSS JOINS

A cross join is referred to as a Cartesian product, which means, for each element in set-A pick all the values from set-B

select t1.f1, t2.f1
from t1 cross join t2

select t1.f1, t2.f1
from t1,t2

Both the above queries are same. The output of this query will be 100 rows. This is because, for each row in t1, all the rows in t2 would be matched. Unless you know what you are doing, you got to be careful in building your queries, to ensure you are not working on Cartesian products.

I am not sure of a real business need for a Cartesian product (I can be wrong as ever), but I know mathematically it is valid to have the same y values for different values of x (m=0), basically a horizontal line on the Cartesian plane.

Joins could be as complicated/confusing in real world scenarios, but if your fundamental understanding of how they behave is clear, you should be able to handle any situation.

Thankz for reading!

Total article views: 18414 | Views in the last 30 days: 29
 
Related Articles
FORUM

Insert - Exec Select statements

Insert - Exec Select statements

FORUM

Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

DYNAMIC SELECT & INSERT QUERY HELP

DYNAMIC SELECT & INSERT QUERY HELP

FORUM

max worker threads

max worker threads

FORUM

max worker threads

max worker threads

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones