Space Ostrich Space Ostrich - 7 months ago 11
SQL Question

What is the difference between "FROM a, b" and "FROM a FULL OUTER JOIN b"?

When working with data from multiple tables, there are a number of different ways that you can

JOIN
those tables, each of which alters the way matching columns are treated. You can also just pull the data from more the one table, i.e.
FROM [table a], [table b]
.

This method seems to still join the tables in some way, and if I had to guess I'd say that this method is simply shorthand for
FULL OUTER JOIN
, but I'm sure there is a difference between the two.

Is the difference simply that
FULL OUTER JOIN
is followed up by
ON [table 1 specific column] = [table 2 specific column]
, or is there something else going on?

Answer

In the first case you apply a CROSS JOIN (or Cartersian Product) - if you don't use a WHERE clause for link your fields (in this case you have an INNER JOIN), in the second case you apply a FULL OUTER JOIN.

DIFFERENCE

With cartesian product you link every row of the first table with every row of the second table

With FULL OUTER JOIN you link rows of the first table with rows of the second table but if a relation is not satisfacted you have a NULL in one of two sides.

EXAMPLES

Suppose you have two tables like these:

CREATE TABLE a (id_a int)
CREATE TABLE b (id_b int)

with these contents:

INSERT INTO A (1)
INSERT INTO A (2)

INSERT INTO B (2)
INSERT INTO B (3)

In the first case, (cartesian product) you'll have:

SELECT * FROM A, B

1 2
1 3
2 2
2 3

In the second case you'll have:

SELECT * FROM A FULL OUTER JOIN B
ON A.ID_A = B.ID_B

1    NULL
2    2
NULL 3

If you write:

SELECT * FROM A,B WHERE A.ID_A = B.ID_B 

is the same of this:

SELECT * FROM A JOIN B ON A.ID_A = B.ID_B

With this result:

2 2
Comments