# LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

## LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

## XML Feeds

 « Getting the list of parameters from a stored procedure by using sqlCmd.Parameters or INFORMATION_SCHEMA.parameters MERGE bug when Foreign Key constraint is created on non-clustered index (2008RTM) »

# Difference between a cross join and a full outer join

We are interviewing for some SQL developer positions and it seems that most people that I interviewed the past 3 weeks do not know the difference between a full outer join and a cross join.

## Cross join

A cross join produces the Cartesian product of the tables involved in the join. 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

Here what Wikipedia has to say about it: http://en.wikipedia.org/wiki/Cartesian_product

For example, the Cartesian product of the 13-element set of standard playing card ranks {Ace, King, Queen, Jack, 10, 9, 8, 7, 6, 5, 4, 3, 2} and the four-element set of card suits {♠, ♥, ♦, ♣} is the 52-element set of all possible playing cards: ranks × suits = {(Ace, ♠), (King, ♠), ..., (2, ♠), (Ace, ♥), ..., (3, ♣), (2, ♣)}. The corresponding Cartesian product has 52 = 13 × 4 elements. The Cartesian product of the suits × ranks would still be the 52 pairings, but in the opposite order {(♠, Ace), (♠, King), ...}. Ordered pairs (a kind of tuple) have order, but sets are unordered. The order in which the elements of a set are listed is irrelevant; the deck can be shuffled and it is still the same set of cards.

## Full outer join

A full outer join includes all rows from both tables, regardless of whether or not the other table has a matching value.

Here is what wikipedia has on full outer join: http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join

Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee.

## Examples

Time to look at some code. First create these two really simple tables

tsql
1. CREATE TABLE TableA (IDA int)
2. GO
3.
4. INSERT TableA VALUES(1)
5. INSERT TableA VALUES(2)
6. INSERT TableA VALUES(3)
7. GO
8.
9.
10. CREATE TABLE TableB (IDB int)
11. GO
12. INSERT TableB VALUES(2)
13. INSERT TableB VALUES(3)
14. INSERT TableB VALUES(4)
15. GO

As you can see both table have 3 rows but only 2 rows are common to both tables. If we do a full outer join now, we will get back a result set that has 4 rows. We get 2 rows that are common to both tables, then we get 1 row from TableA which does not exist in TableB, we also get 1 row from TableB which does not exist in TableA

tsql
1. SELECT * FROM TableA a
2. FULL OUTER JOIN TableB b on a.IDA = b.IDB

Here is the output

IDAIDB
1NULL
22
33
NULL4

As you can see there are two rows that have NULL in them, these are the ones that don't exist in both tables

Now, let's look at the cross join. We will get back 9 rows since we have 3 rows in both tables, output will be 3 x 3 rows

tsql
1. SELECT * FROM TableA a
2. CROSS JOIN TableB b
3. ORDER BY a.IDA,b.IDB

Here is the output

IDAIDB
12
13
14
22
23
24
32
33
34

As you can see for each row in TableA, you will get back all 3 rows from TableB

I got all kind of creative answer in responds to this question, some people were describing the cross join as an inner join or describing both join as being the same

If you want to read a little more about joins and a different way of doing a cross join, check out these two posts SQL Advent 2011 Day 15: Joins and SQL Advent 2011 Day 16: CROSS APPLY and OUTER APPLY

6215 views

Comment from: james [Visitor]
Id have got this right!

...and an excellent question to add to the list for future recruitments
02/12/12 @ 08:33

I got all kind of creative answer in responds to this question, some people were describing the cross join as an inner join

Those people weren't so far from the truth. Conceptually, a cross join is the same thing as an inner join -- one without a join condition. In other words, an inner join is equivalent to doing a cross join and then applying the join condition (ON clause, or WHERE clause in the old syntax). In fact, at least for some DBMSs, and I believe in the ANSI standard, the CROSS keyword is entirely optional, effectively synonymous with INNER (which is also optional) - that is, FOO INNER JOIN BAR or FOO JOIN BAR is exactly the same thing as FOO CROSS JOIN BAR. It's just that stylistically you'd use the latter to emphasize the lack of a join condition when you actually want a total cartesian product. With the old-style syntax it was all to easy to accidentally do an unqualified cross join by just doing SELECT .. FROM FOO,BAR and forgetting to put the appropriate condition in the WHERE clause; at least the newer syntax helps you remember to make your intention explicit.

An outer join is a funny kind of animal - you could think of it as starting with an inner join, then adding the rows from one (left OJ) or the other (right OJ) or both (full OJ) tables that didn't match condition X in the original join, padded out appropriately with nulls. In any case it's sort of an augmentation of an inner join, not an entirely different concept.

Having interviewed a lot of self-described SQL experts myself, I can attest to the reigning confusion in this area.
02/12/12 @ 12:01
Comment from: SQLDenis [Member]

You are right about the old style join syntax, I have seen that happen far too often where a cross join would be executed because someone forgot to add the WHERE condition
02/12/12 @ 12:35