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

Question

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?

Webfooted

This question has already been asked, however, the question was asked with additional variables that confused the possible answer, and the answer itself, while accurate, did not go into enough detail to resolve that confusion. As I cannot comment on the question to ask for clarification, I'm asking a new one. If the answer reveals that THIS question has additional variables, those variables will be removed from this question to avoid confusing future readers. Original question: lin – Webfooted

Answer 1

Your question has been answered, but from your comments I gather that you are still insecure whether you have understood the matter completely. So, I thought I'd just add another answer :-)

Let's start with the simple

FROM a, b

This is an antiquated join syntax that was replaced by explicit joins in Standard SQL-1992. With the above, you had to put the join criteria, if any, in the WHERE clause. Without join criteria in the WHERE clause this is a cross join, which you would now explicitly write as

FROM a CROSS JOIN b

This tells the reader that you purposly want all combinations of a and b (and haven't only forgotten the join criteria or deleted it mistakenly). An example is

FROM store CROSS JOIN product

Here you combine every store with every product, no matter whether the store really has the product; you simply show all possible combinations. With two stores and two products, a result could look as follows:

store   product
s1      p1
s1      p2
s2      p1
s2      p2

A CROSS JOIN is something rarely needed. In above case we might want to know all store product/combinations and select a 'yes' or 'no' for every line, so we see which products a store features and which not.

In a relational database we usually deal with table's relations, however, so let's add join criteria:

FROM a, b
WHERE a.col1 = b.col2

This is an inner join, where we only look for record matches. This is now written as

FROM a
INNER JOIN b ON a.col1 = b.col2

or (omitting the optional keyword INNER, as a join is an inner join by default):

FROM a
JOIN b ON a.col1 = b.col2

Here is an example. We have two tables containing the expenses and earnings per department and year.

FROM dept_cost
JOIN dept_gain ON dept_gain.dept_no = dept_cost.dept_no AND dept_gain.year = dept.cost.year

Lets's say the tables contain:

year   dept_no   total_cost
2015   d001      20000
2016   d001      25000
2016   d002      10000

and

year   dept_no   total_gain
2015   d001      40000
2015   d002      30000
2016   d001      50000

Then a result would be:

year   dept_no   total_cost   total_gain
2015   d001      20000        40000
2016   d001      25000        50000

because only 2015/d001 and d001/2016 are found in both tables.

If you want to see the other data, too, you must outer join. You can outer join dept_gain to dept_cost, so as to see all costs - along with their gains if any. Or, vice versa, you outer join dept_cost to dept_gain, so as to see all gains - along with their costs if any. Or you full outer join, so as to see all data:

FROM dept_cost
FULL OUTER JOIN dept_gain ON dept_gain.dept_no = dept_cost.dept_no 
                          AND dept_gain.year = dept.cost.year

year   dept_no   total_cost   total_gain
2015   d001      20000        40000
2015   d002                   30000
2016   d001      25000        50000
2016   d002      10000        

Both the CROSS JOIN and the FULL OUTER JOIN are rarely needed. So don't worry if you don't understand them, yet. You will usually only need the INNER JOIN and sometimes the LEFT OUTER JOIN.

Verdie

And with this, it clicked. Very well written and definitely something I'll be keeping open in a tab if I'm having trouble. – Webfooted

Answer 2

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

Accompany

Would that make the first method shorthand for INNER JOIN then? – Webfooted

@SpaceOstrich: No. The first version is a CROSS join, not an inner join – Sami

@SpaceOstrich: I'va added for complete informations, the case about INNER JOIN (not involved in the question) – Accompany

So if I'm understanding this right, in a CROSS JOIN the two tables are just matched by "index number". Rather than having a shared column that the data is merged by? – Webfooted

I consider a cross join an inner join, without join condition. (All rows combined, no relations, no "index number".) – Birdcage

I think I've pretty much got it, wish I could use new lines in comments so I could format a table but oh well. I'm still a bit confused but given I've been doing SQL for 6 hours at the most, I imagine it'll "click into place" soon enough. Marking this as resolved, I may revisit my question to reformat it in the future, for ease of use by new programmers. Explanations of syntax and whatnot. – Webfooted

@SpaceOstrich: there is no "index number" matching in a cross join. It's simply the cartesian product of both tables: en.wikipedia.org/wiki/Cartesian_product – Sami

nit: "Cartesian" Product – Cabalistic