6

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?

Improve this question
1
  • 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 Commented May 3, 2016 at 6:34

2 Answers 2

Reset to default
15

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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

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
Improve this answer

8 Comments

Would that make the first method shorthand for INNER JOIN then?
@SpaceOstrich: No. The first version is a CROSS join, not an inner join
@SpaceOstrich: I'va added for complete informations, the case about INNER JOIN (not involved in the question)
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?
I consider a cross join an inner join, without join condition. (All rows combined, no relations, no "index number".)
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.