6.4.1.1 JOIN Syntax

MySQL supports the following JOIN syntaxes for use in SELECT statements:

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference RIGHT [OUTER] JOIN table_reference
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Where table_reference is defined as:

table_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]

and join_condition is defined as:

ON conditional_expr |
USING (column_list)

You should generally not have any conditions in the ON part that are used to restrict which rows you have in the result set (there are exceptions to this rule). If you want to restrict which rows should be in the result, you have to do this in the WHERE clause.

Note that in versions before Version 3.23.17, the INNER JOIN didn't take a join_condition!

The last LEFT OUTER JOIN syntax shown above exists only for compatibility with ODBC:

• A table reference may be aliased using tbl_name AS alias_name or tbl_name alias_name:

  mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
      ->        WHERE t1.name = t2.name;
  

• The ON conditional is any conditional of the form that may be used in a WHERE clause.
• If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

  mysql> SELECT table1.* FROM table1
      ->        LEFT JOIN table2 ON table1.id=table2.id
      ->        WHERE table2.id IS NULL;
  

  This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course. See section 5.2.7 How MySQL Optimises LEFT JOIN and RIGHT JOIN.
• The USING (column_list) clause names a list of columns that must exist in both tables. A USING clause such as:

  A LEFT JOIN B USING (C1,C2,C3,...)
  

  is defined to be semantically identical to an ON expression like this:

  A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
  

• The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.
• INNER JOIN and , (comma) are semantically equivalent. Both do a full join between the tables used. Normally, you specify how the tables should be linked in the WHERE condition.
• RIGHT JOIN works analogously as LEFT JOIN. To keep code portable across databases, it's recommended to use LEFT JOIN instead of RIGHT JOIN.
• STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases where the join optimiser puts the tables in the wrong order.
• As of MySQL Version 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. This is useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the possible indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. In MySQL 4.0.9 you can also use FORCE INDEX. This acts likes USE INDEX (key_list) but with the addition that a table scan is assumed to be VERY expensive. In other words a table scan will only be used if there is no way to use one of the given index to find rows in the table. USE/IGNORE KEY are synonyms for USE/IGNORE INDEX.

Some examples:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    ->          LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    ->          WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    ->          WHERE key1=1 AND key2=2 AND key3=3;

See section 5.2.7 How MySQL Optimises LEFT JOIN and RIGHT JOIN.

User Comments

An example of an inner join on more than two tables
would be useful here. And maybe an explanation of
why the following is important.

If the order is not correct you'll get a syntax error. If
you have a FROM clause with the following pattern:

FROM table1 INNER JOIN (some other join) ON (join
condition).

Change it to:
FROM (some other join) INNER JOIN table1 ON (join
condition).

The join_condition is used for winnowing the
rows of the "right half" that you want to use for
the cross, whereas the WHERE clause is used for
winnowing the composite.

For example, suppose we have a table Men (Man
varchar(15), Lover(15)) and we want to find all
men who had Daisy as a Lover, but not Petunia.
Then we might use:

SELECT M1.Man FROM Men AS M1 LEFT JOIN
Men AS
M2
ON M1.Man=M2.Man AND M2.Lover='Petunia'
WHERE M1.Lover='Daisy' AND M2.Lover IS NULL;

The second part on the ON ensures that we get
NULLs on the right side when Petunia is missing
while the second part of the where picks out
those rows where we actually did get the NULL.

You can refer to the same table more than once by using table aliases. A common example would be when you have a staff table that contains both staff and manager's details. If you wanted a list of staff, with their corresponding manager's details as well, you would refer to the same table twice.
eg.
SELECT s1.StaffId, s1.StaffName, s1.Position, s1.ManagerId, s2.StaffName AS ManagerName, s2.Position AS ManagerPosition
FROM staff AS s1 INNER JOIN staff AS s2 ON(s1.ManagerId=s2.StaffId)

This can work multiple times, referring back to the same table. You can also join with other tables as normal at the same time, although you need to specify which table alias (and thus which rows, eg. the manager's or the staff's) you would like to join on.

You want to select all rows from table1 which don't exist in table 2?
Example:
table1.id = list(1, 2, 3, 4, 5)
table2.id = list(1, 2, 5)
You want to have 3 and 4 as the result cause they are not in table 2?
Use the OUTER JOIN:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL

Note that the form USING (X,Y) does not give the same results as the long form e.g. U ON (W.X = U.X AND W.Y = U.Y) when LEFT JOINing multiple tables. I suppose this is because with USING
the left table is used for the comparison whereas with ON the specified table is used ...

This comment got deleted at some point so I'm
adding it back. Probably simular to comment 1.
This gave me trouble for a while so I thought I'd
pass it on. If you want a left join with a
condition for the right table (the row that may
or may not be there) you have to put the
condition in the join clause, not the where
clause.

Here's an example:
SELECT
*
FROM
t1 LEFT JOIN t2 on (t1.id_t1 =
t2.id_t1
AND t2.id_t2 = 345)
WHERE
t1.id_level=17 ;

I appologize if that's common knowledge but this
note would have saved me hours.
Thanks

Thank you David Martin, as your 'obsolete' note
helped me finish my query :)

I've seen requests for joining more than two
tables.
This goes a little further in that it joins three tables,
one of which is aliased three times.

The advert table contains adverts in which there
are
three region fields - region1,region2,region3; and a
category field.

Both the region and category tables have a key
id
field to join to the adverts table and a description
field.

The SQL is as follows...


SELECT adverts.*, categories.description AS
cat_desc, regions_1.description AS
region1_desc,regions_2.description AS
region2_desc,regions_3.description AS region3_desc
FROM adverts
RIGHT JOIN regions AS regions_3 ON
regions_3.regionID=adverts.region3
RIGHT JOIN regions AS regions_2 ON
regions_2.regionID=adverts.region2
RIGHT JOIN regions AS regions_1 ON
regions_1.regionID=adverts.region1
INNER JOIN categories ON
categories.categoryID=adverts.categoryID

This results in records showing all adverts with
the relevant descriptive text for each region and the
category.
It is a little slow, so if any one knows of a way
to speed it up...

Why do you need JOINs for the above example? Couldn't you just do:

SELECT adverts.*, categories.description AS
cat_desc, regions_1.description AS
region1_desc, regions_2.description AS
region2_desc, regions_3.description AS region3_desc
FROM adverts, regions AS regions_1, regions AS regions_2, regions AS regions_3, categories
WHERE regions_3.regionID=adverts.region3
AND regions_2.regionID=adverts.region2
AND regions_1.regionID=adverts.region1
AND categories.categoryID=adverts.categoryID;

Or am I insane?

Tip time:

(Background: This database is used to keep track of scores for students in my classes.)

So in this case, I have three tables, one has student's "codename" (as posting their real name on the web is a no-no) and an index (there is more data in this table, but this is all you really need to know.) Then there's a table with the assignments, containing the assignment name, and an index for each assignment. Finally, there is a scores table, which has for each paper I get turned in, a student_id (releated to the student index) an act_id (related to the assignments index) and a score.

It looked something like this:
students table:
+----+---------------+
| id | codename |
+----+---------------+
| 1 | Budy |
+----+---------------+

assignments table:
+--------+------------+
| act_id | name |
+--------+------------+
| 1 | Activity 1 |
| 2 | Activity 2 |
+--------+------------+

scores table:
+------------+--------+-------+
| student_id | act_id | score |
+------------+--------+-------+
| 1 | 1 | 10 |
| 1 | 2 | 10 |
+------------+--------+-------+

Now the problem was, I wanted to have the assignments listed across the top, and the scores next to the names. Something like this:
+---------------+------------+------------+-------+
| codename | Activity 1 | Activity 2 | Total |
+---------------+------------+------------+-------+
| budy | 10 | 10 | 20 |
+---------------+------------+------------+-------+

So here's how the sql statement ended up:
SELECT names.codename,
s1.score AS "Score1", s1.comment AS "Comments1",
s2.score AS "Score2", s2.comment AS "Comments2",
SUM(st.score) AS "Total"
FROM students names
LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id
LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id
LEFT JOIN scores st ON names.id=st.student_id
WHERE names.codename <> ''
GROUP BY names.codename
ORDER BY names.codename;

As you can see, for each activity, I need to add another left join, but it looks exactly like the last one, thus it is easy to build through a program like php. I hope this helps someone out.

equivalent of *= in mySQL

I was trying to convert this SQL statement to mySQL:

$sql = "select * from table1,table2 where topicID = '$topicID' and table1.CommentID *= table2.replyCommentID order by recordID asc" ;

this is what I came up with after much trial & error:

$sql = "select * from table1 LEFT JOIN table2 ON table1.commentID=table2.replyCommentID where table1.topicID = '$topicID' order by recordID asc " ;

here it is in action: http://www.groovything.com/default.php?menu=c&subM=d_f&css=05&page=forum&action=view&topicID=1

Hopefully this will be useful info. I was wishing it was out there when I needed it so I decided to post it in case anyone else has a similar question

How can I outer join 3 independent tables using one central table?

I cannot use: table1 Left Join .. table2 Left Join .. table 3 Left Join .. table4 because each table needs to be connected to the previous tables.

I want to join using this logic -
table 1 left join table 2,
table 1 left join table 3,
table 1 left join table 4

(similar to the outer join (+) in Oracle)

I came across this problem several times and I have not found a solution yet. I would appreciate any help!

yael.goldberg@kidbiz3000.com

I use left joins to generate sums on one table using different conditions:
t1 to make sure that ALL grouped records are shown
t(n+1) for use per condition
and as mentioned above, the JOIN condition must be used as well for the primary key AND for the condtion per sum!

Here is an example:

drop table if exists testtable;
create table testtable
(mykey int not null,
mygroup int,
cond int,
value int,
primary key (mykey));

insert into testtable
values (1, 1, 1, 5), (2, 1, 1, 6), (3, 1, 2, 3), (4, 2, 2, 4), (5, 3, 3, 5);

-- returns nothing
select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3
from testtable t1
left join testtable t2 on t1.mykey=t2.mykey
left join testtable t3 on t1.mykey=t3.mykey
left join testtable t4 on t1.mykey=t4.mykey
where t2.cond=1
and t3.cond=2
and t4.cond=3
group by 1
order by 1;

-- returns correct sums
select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3
from testtable t1
left join testtable t2 on t1.mykey=t2.mykey and t2.cond=1
left join testtable t3 on t1.mykey=t3.mykey and t3.cond=2
left join testtable t4 on t1.mykey=t4.mykey and t4.cond=3
group by 1
order by 1;

mygroup | cond_1 | cond_2 | cond_3
1 | 11 | 3 | 0
2 | 0 | 4 | 0
3 | 0 | 0 | 5

I want to excute select cross query in mySQL.

I have the following table:
tblTest (Id Int, Memory Varchar(50), Content Text, User Varchar(2))
Primary key(Id, Memory)

I insert some records into this table:
Insert into tblTest Values(1, 'AAA', 'Content AAA1', 'usr1'),
Values(1, 'BBB', 'Content BBB1', 'usr1'),
Values(1, 'CCC', 'Content CCC1', 'usr1')

Insert into tblTest Values(2, 'AAA', 'Content AAA2', 'usr2'),
Values(2, 'BBB', 'Content BBB2', 'usr2'),
Values(2, 'CCC', 'Content CCC2', 'usr2')
.........................


I want excute cross query that result of that query is:
Fields: Id AAA BBB CCC User
Record 1: 1 Content AAA1 Content BBB1 Content CCC1 usr1
Record 2: 2 Content AAA2 Content BBB2 Content CCC2 usr2
......... ....................................................................................


How "Select query" do I can write?
Please help me!
Thanks!

This might also be common knowledge, but this comment would have saved me a few hours: You can join tables by their varchar-columns using LIKE.

I have a table of persons with their phone areacodes (all VARCHAR). Example:
Person | Areacode
Smith | 0202
Miller | 0333

I have another table with the range-codes for each areacode (all VARCHAR). Example:
Source-Area | Destination-Area | Range-code
0201 | 0201 | City
0201 | 0202 | City
0201 | 03 | Far

As you can see, the destination area 03 is short for all areacodes beginning with 03. I need a query which joins the persons to the destination areas. Following the example, if I call "Miller" from area 0201, I want to get range-code "Far".

This is how to do it:
SELECT Rangecode
FROM Persons p, Areas a
WHERE p.Areacode LIKE CONCAT(a.DestinationArea, "%")

Where is FULL OUTER JOIN???

So I will summarize that only three joins actually exists as of now (and as stated by the beginning of this page):

[CROSS] JOIN
INNER JOIN
LEFT [OUTER] JOIN

Then what is the difference between LEFT and RIGHT JOINS, recommends not to use RIGHT, why?

As in Oracle the sign + is placed on the side where the data in the column is missing and to include the column, to have a join - Here it can be done with LEFT itself and felt the absurdity to have could be the reason behind it! Oooops!!!!!

I also think that the missing feature of FULL OUTER JOIN is a real drawback to MySQL. However, from MySQL 4 on you can use a workaround using the UNION construct. E.g. at
http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html

in response to "ewiner", as mentioned above in the docs,

"INNER JOIN and , (comma) are semantically equivalent. Both do a full join between the tables used"

Add your own comment.