# A Refresher on Joins

,

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)

```t1t2t3
f1f1f1
------
1
22
33
4
5
666
77
88
99
1010
11
1212
1313
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.