visualizing inner joins with three tables

Question

Can someone explain the difference between these 2 types of joins and how to visualize them? Not sure when to use which...

Ex1

select a.f1, a.f2, b.f1, b.f2
from table_a a
inner join table_c c
    on a.id = c.id
inner join table_b b
    on c.id = b.id

Ex 2

SELECT a.au_lname,
       a.au_fname,
       t.title
FROM   authors a
       INNER JOIN titleauthor ta
         ON a.au_id = ta.au_id
       JOIN titles t
         ON ta.title_id = t.title_id
WHERE  t.type = 'trad_cook'
ORDER  BY t.title ASC

Using this unrelated venn diagram- what do these 2 queries return?

enter image description here

If you mean difference between `INNER JOIN` and `JOIN`, there are no difference. — Hamlet Hakobyan, Jan 16 '13 at 15:58
I don't see any significant difference in your two examples. — Martin Smith, Jan 16 '13 at 15:58
What is the difference between a duck? One of its legs are both the same! — , Jan 16 '13 at 16:03
The second query limits the result to rows with `t.type = 'trad_cook'` whereas the first query does not have a `where` clause at all. Apart from that there is no real difference. — , Jan 16 '13 at 16:06
okay thank you. using the venn diagram above what portion of the diagram am i returning? — PartOfTheOhana, Jan 16 '13 at 16:09

score 2 · Accepted Answer · answered Jan 16 '13 at 15:57

2

It simply means that you are joining three tables in your SELECT statement.

authors is joined with titleauthor
titleauthor is joined with titles

To learn more about joins, please see the article below,

Visual Representation of SQL Joins

answered Jan 16 '13 at 15:57

John Woo

258,903
69
498
492

I get it with 2 tables, not 3 though... what does the join look like in the 3 case? – PartOfTheOhana Jan 16 '13 at 16:00
2

@nivyaj - Both of the examples you posted join 3 tables. – Martin Smith Jan 16 '13 at 16:01
@nivyaj then what are these: `authors`, `titleauthor`, and `titles`? – John Woo Jan 16 '13 at 16:02
sorry i get the visualization with 2 tables, not 3 though. Can you take a look at the venn diagram i added and explain the join in that context? – PartOfTheOhana Jan 16 '13 at 16:07
1

@nivyaj - So it would be nerd in the case of your Venn diagram as it is the intersection of all three tables although TBH using Venn diagrams to visualise joins is a flawed concept. – Martin Smith Jan 16 '13 at 16:09
flawed because it assumes unique records only, right? or is there another reason? – PartOfTheOhana Jan 16 '13 at 16:11
Because it assumes unique records and also because it can't represent key concepts such as cross joins and non equi joins and can also cause additional confusion as in [the question posed here](http://stackoverflow.com/q/13997365/73226) – Martin Smith Jan 16 '13 at 16:13

visualizing inner joins with three tables

1 Answers1