Mysql join syntax

Question

If I want to perform joins on 3 or more tables, what is the best syntax? This is my attempt:

Select * 
from table1 
inner join table2 using id1, table2 
inner join table3 using id2, table3 
inner join table4 using id4 
where table2.column1="something" 
and table3.column4="something_else";

does that look right? The things I'm not sure about are 1) do I need to seperate the joins with a comma 2) am I right to make all my joins first and then put my conditions after that? 3) would I be better to use sub-queries and if so what is the corect syntax

Thanks for any advice!

Answer 1

1) do I need to seperate the joins with a comma

No

2) am I right to make all my joins first and then put my conditions after that?

Yes

3) would I be better to use sub-queries and if so what is the corect syntax

No. Joining tables is the preferred and correct way.

Answer 2

1. Joins are not separated by a comma
2. ANSI syntax puts the joins first then where condition

e.g. SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE table2.column1='Something'

1. I'm not 100% sure what you are trying to achieve. But it looks like you do not need to use subqueries.

A subquery would be executed for every row, it sounds as though you could run a more efficient query just using inner joins.

Hope that helps. If you can elaborate a little I will provide more explanation.

Answer 3

Remove the comma's and the duplicate table names, like:

Select * 
from table1 
inner join table2 using id1
inner join table3 using id2
inner join table4 using id4 
where table2.column1="something" 
and table3.column4="something_else"

If id4 has a different name in table1, explicitly name the join condition, for example:

inner join table4 on table4.id = table1.table4i

Answer 4

Given your requirement that table2 gets joind on the id1-columns in table1 and table2, table3 gets joind on the id2-columns in table2 and table3 and table4 gets joind on the id3-columns in table3 and table4 you'll have to do:

SELECT * 
FROM table1 
INNER JOIN table2 ON table2.id1 = table1.id1
INNER JOIN table3 ON table3.id2 = table2.id2
INNER JOIN table4 ON table4.id3 = table3.id3
WHERE table2.column1 = "something" 
    AND table3.column4 = "something_else"

I think this statement is much more clearer on what is exactly joined in which way - compared to the USING-statement.

Answer 5

1. Try to avoid using * where possible.
   Specify exactly the data you want returned.

2. Format your queries using a standard style.
   Pick a style you like and keep to it.
   You will thank yourself later when your queries get more complex.

3. Most optimizers will recognize when a condition in a WHERE clause implies an INNER JOIN, but there's no reason not to code that explicitly; if nothing else it keeps your WHERE clause manageable.

4. Be explicit about what columns you join on. Be explicit about the type of join you're using. USING seems like a shortcut that could get you into trouble.

5. MySQL has traditionally not handled subqueries as well as could be hoped. That may be changing in newer versions, but there are other ways to get your data without relying on them.

Welcome to the wonderful world of relational databases!

select t1.*
       , t2.*
       , t3.*
       , t4.*

from   table1 t1

       inner join table2 t2
       on t1.id = t2.t1_id
    and
       t2.column1 = "something"

       inner join table3 t3
       on t2.id = t3.t2_id
    and
       t3.column4 = "something_else"

       inner join table4 t4
       on t3.id = t4.t3_id;

Answer 6

You may be able to use natural join which joins on field names common to the tables you want to join as follows.

SELECT * FROM table1 NATURAL JOIN table2 NATURAL JOIN table3 NATURAL table4 WHERE table2.column1 = "something" AND table3.column4 = "something_else"