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?

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
``````