MySQL Joins - Placement of the ON clause

Question

I recently tried to pull some results and was struggling to work out the correct query to do it. All the examples I have seen and even my MySQL book show joins as follows:

SELECT * 
FROM table_1
LEFT JOIN table_2 LEFT OUTER JOIN table_3 LEFT JOIN table_2
ON table_1.id = table_2.rel_id
AND table_1.id = table_3.rel_id
AND table_3.id = table_2.rel_id
WHERE table_1.some_col = some_vale;

This didn't work and instead, the solution was thus:

SELECT *
FROM table_1
LEFT JOIN table_2 ON table_1.id = table_2.rel_id
LEFT OUTER JOIN table_3 ON table_1.id = table_3.rel_id
LEFT JOIN table_2 ON table_3.id = table_2.rel_id
WHERE table_1.some_col = some_value;

What is the difference between these two SELECT statements? When should each be used? How do

you determine how the joins work?

Are you sure that works? When I run code like that, I get this: "ERROR 1066 (42000): Not unique table/alias: 'table_2'" for mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (x86_64) using readline 6.2 — joelparkerhenderson, Apr 05 '12 at 08:04
The difference is that first query have syntax errors. The second one is almost correct; just add table aliases, there are two `table_2`. — Devart, Apr 05 '12 at 08:07
What the others said. The `ON` go with with the joins. You can have multiple conditions in the ON part, but not really for joining multiple tables. — Cylindric, Apr 05 '12 at 08:49

score 0 · Accepted Answer · answered Apr 05 '12 at 08:10

First statement didn`t work because you join table_2 twice with some name and without condition in first time. Left outer join in mysql is fully equivalent left join. Eventually first statement fully incorrect.

when you join table, you must use "ON" clause or describe join condition in WHERE clause. If you join one table twice you must add aliases to them like "left join table_2 as t2 left join table_2 as t22".

Anyway docs will help you:)

mysql select statement

xkeshav · Answer 2 · 2012-04-05T08:39:03.123

You must read MYSQL JOIN Syntax

Basic Syntax

     SELECT * FROM first_table ft  
     (LEFT | RIGHT )JOIN second_table st ON st.column = ft.column
     (LEFT | RIGHT )JOIN third_table tt ON tt.column = ft.column (OR st.column)
     WHERE

Note : Default JOIN is INNER JOIN alternatively you can use below syntax

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

MySQL Joins - Placement of the ON clause

2 Answers2