FULL OUTER JOIN vs CROSS JOIN
>>> Try our online JOIN Types Course Today!
As I have been working on the SQL Server JOIN Types poster, I have received several questions around the difference between a CROSS JOIN, and a FULL OUTER JOIN. After looking at the Venn diagrams for the two, they are both shown as the same, however they are not the same by any means.
Yes, they both include all rows from both the LEFT and RIGHT side of the JOIN, however they are matched up or JOINed in a very different way.
Lets take a look, first at the Venn diagrams, you can see below that the Venn diagrams show that all rows from Table1 are included in the results, and all rows from Table2 are included in the results. This is indeed correct, the differences come in the number of rows and how the results are matched up.
Now lets take a look at some sample code that shows the differences. First we will create a database and create a couple tables to use for the demo, and each table has 3 rows inserted to it:
CREATE DATABASE [QueryTraining];
GO
USE [QueryTraining];
GO
CREATE TABLE People
(
id INTEGER IDENTITY NOT NULL,
Name VARCHAR(100) NOT NULL,
fk INTEGER NULL
PRIMARY KEY (id)
) ;
CREATE TABLE Colors
(
id INTEGER NOT NULL,
FavoriteColor VARCHAR(100) NOT NULL
) ;
INSERT INTO Colors (id, FavoriteColor) VALUES
(1, 'red'),
(2, 'green'),
(3, 'blue');
INSERT INTO People (Name, fk) VALUES
('Steve', 1),
('Aaron', 3),
('Mary', NULL);
Look at this as a database to store people and their favorite color, in this example, there are 3 people , and 3 colors. One person does not have a favorite color, and one color has not been favorited by anyone.
Now lets take a look at the differences between the two.
CROSS JOIN
The CROSS JOIN gives you the Cartesian product of the two tables, by matching every row from one table with every row from another table. If there are X rows in the first table, and Y rows in the second table, the result set will have exactly X times Y rows. Notice on the CROSS JOIN, there is no ON clause specified. This is because there is not matching.
CROSS JOIN Sample
SELECT *
FROM People p
CROSS JOIN Colors c;
Which gives us the following result set:
In the above result set, you can see that for each row in the People table (3) it was matched up with each row from the Colors table (3) which gives us a total of 3 x 3 = 9 rows in the result set.
FULL OUTER JOIN
Now for the full outer JOIN, this is like asking for a LEFT OUTER JOIN and a RIGHT OUTER JOIN in the same query. You are asking to get every row from Table1 matching what could be matched in Table2, for those that don’t match on the Table1 side, just display the Table2 side, with NULLs on the LEFT side, and for those that don’t match on the TABLE2 side, just display the Table1 side with NULLs on the RIGHT side.
FULL OUTER JOIN Sample
SELECT *
FROM People p
FULL OUTER JOIN Colors c
ON p.fk = c.id;
In this case everything from Table1 will be represented, and everything from Table2 will be shown, but they will be matched up base on the ON clause, rather than with the CROSS JOIN which gives every possible combination.
I hope that this helps answer some of the questions with the differences between a CROSS JOIN and a FULL OUTER JOIN.
If you haven’t already taken a look, please check out the free download of the SQL Server JOIN Types poster.
For More Information:
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
thank you . lots of effort put in , cool work.
But both ARE identical if you don’t filter ON p.fk = c.id :
with
colors as
(
select 1 id, ‘red’ color from dual union
select 2, ‘green’ from dual union
select 3, ‘blue’ from dual
)
, people as
(
select ‘Steve’ name, 1 fk from dual union
select ‘Aaron’, 3 from dual union
select ‘Mary’, NULL from dual
)
—
select *
from colors
outer full join people on 1=1
——————-
1 red Aaron 3
1 red Mary (NULL)
1 red Steve 1
2 green Aaron 3
2 green Mary (NULL)
2 green Steve 1
3 blue Aaron 3
3 blue Mary (NULL)
3 blue Steve 1
What’s more, without this filtering, even not full outer join,
and also inner join, are identical:
…
select *
from colors
outer join people on 1=1
…
select *
from colors
inner join people on 1=1
Thanks..i understood clearly:)
nice explanation
FULL OUTER JOIN and CROSS JOIN do not return the same result if one of the tables is empty. For instance if the first table has 4 rows and the second table has 0 rows, then FULL OUTER JOIN gives a result with 4 rows and all of the columns from the second table contain NULLs, while CROSS JOIN returns 0 rows.
Kim – Thanks for the clarification, that is a good edge case that shows the difference.
-Steve Stedman