In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at joins. SQL Server supports the ANSI SQL-92 (new style) syntax and the ANSI-SQL-89 (old style) syntax. Let’s take a look at both for INNER, OUTER and CROSS joins. This post is not about JOIN fundamentals, the internet is full of those and I won’t go into too much detail here, this post is mostly about showing you the difference between new and old style syntax.

Before we start, create these two extremely simple tables

CREATE TABLE Test1(id int)
go

INSERT Test1 VALUES(1)
INSERT Test1 VALUES(2)
INSERT Test1 VALUES(3)
INSERT Test1 VALUES(4)
INSERT Test1 VALUES(5)


CREATE TABLE Test2(id int)
go

INSERT Test2 VALUES(1)
INSERT Test2 VALUES(2)
INSERT Test2 VALUES(3)
INSERT Test2 VALUES(4)
INSERT Test2 VALUES(6)

As you can see, each table has one row that does not exists in the other table

The INNER JOIN

Here is how you do an INNER JOIN in the old style syntax

SELECT * FROM Test1 t1,Test2 t2
WHERE t1.id = t2.id 

Here is how you do it with the new style syntax

SELECT * FROM Test1 t1
JOIN Test2 t2
ON t1.id = t2.id 

Both will return the following results set

id id
1   1
2   2
3   3
4   4

The danger with the old style syntax is that you can forget the WHERE clause, with the new style syntax you will get an error

SELECT * FROM Test1 t1
JOIN Test2 t2

_Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘t2’._

Now run this

SELECT * FROM Test1 t1,Test2 t2 

Output

id id
1   1
2   1
3   1
4   1
5   1
1   2
2   2
3   2
4   2
5   2
1   3
2   3
3   3
4   3
5   3
1   4
2   4
3   4
4   4
5   4
1   6
2   6
3   6
4   6
5   6

As you can see, that created a Cartesian Product or CROSS JOIN, in the next section we will take a look at that

The CROSS JOIN

A CROSS JOIN or Cartesian Product will take each row in one table and match that with all rows from the second table. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table, in our case it is 5 x 5 = 25 rows

Here is the old style syntax we saw before

SELECT * FROM Test1 t1,Test2 t2

Here is the new style syntax

SELECT * FROM Test1
CROSS JOIN Test2

Output

id id
1   1
2   1
3   1
4   1
5   1
1   2
2   2
3   2
4   2
5   2
1   3
2   3
3   3
4   3
5   3
1   4
2   4
3   4
4   4
5   4
1   6
2   6
3   6
4   6
5   6

I like the new style syntax much better, your intentions are clear, I know by looking at the code that the developer wanted a CROSS JOIN, with the old style syntax you always wonder if somehow they forgot the WHERE clause

Here is another way to do a CROSS JOIN, you can use CROSS APPLY

SELECT * FROM Test1
CROSS APPLY Test2

I will cover CROSS APPLY and OUTER APPLY in another post

The OUTER JOIN

The OUTER JOIN will take all rows from one table and match all the rows from the other table, when there is no match a row with NULLS will be returned. Let’s take a look

SELECT * FROM Test1 t1
LEFT OUTER JOIN Test2 t2
ON t1.id = t2.id 

Output

1  1
2   2
3   3
4   4
5   NULL

As you can see for id 5, there is no matching row in table Test2 so a NULL is returned

Here is what the old style syntax looks like, go ahead and run it

SELECT * FROM Test1 t1,Test2 t2
WHERE t1.id *= t2.id 

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘*=‘.

That is right, this syntax is deprecated and you won’t be able to use it anymore, get in the habit of using the OUTER JOIN syntax. BTW OUTER is optional, you can just specify LEFT JOIN, the queries below are identical

SELECT * FROM Test1 t1
LEFT JOIN Test2 t2
ON t1.id = t2.id 


SELECT * FROM Test1 t1
LEFT OUTER JOIN Test2 t2
ON t1.id = t2.id 

The RIGHT JOIN is the same as the LEFT JOIN but reversed

SELECT * FROM Test1 t1
RIGHT JOIN Test2 t2
ON t1.id = t2.id 

id      id
1   1
2   2
3   3
4   4
NULL    6

This two queries will give the exact same output

SELECT t1.*,t2.* FROM Test1 t1
RIGHT JOIN Test2 t2
ON t1.id = t2.id 

SELECT t1.*,t2.*  FROM Test2 t2
LEFT JOIN Test1 t1
ON t1.id = t2.id 

As you can see if you use RIGHT instead of LEFT and flip the tables around, it is the same exact query

Just as with the LEFT JOIN, you can use the old style RIGHT JOIN syntax anymore

SELECT * FROM Test1 t1,Test2 t2
WHERE t1.id =* t2.id 

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘*‘.

A FULL OUTER JOIN will do an OUTER JOIN on both tables, whatever doesn match in either table will get a NULL in the result

SELECT t1.*,t2.*  FROM Test2 t2
FULL OUTER JOIN Test1 t1
ON t1.id = t2.id 

id id
1   1
2   2
3   3
4   4
NULL    6
5   NULL

As you can see each column has one NULL for the row missing in the table.

Just as with LEFT and RIGHT JOINS, OUTER is optional, you can leave it out

SELECT t1.*,t2.*  FROM Test2 t2
FULL JOIN Test1 t1
ON t1.id = t2.id 

That is it for today’s post, come back tomorrow for OUTER and CROSS APPLY