• 6 Справочник по языку MySQL
  • 6.4 Обработка данных: SELECT, INSERT, UPDATE, DELETE
    • 6.4.1 Синтаксис оператора SELECT
      • 6.4.1.1 Синтаксис оператора JOIN
      • 6.4.1.2 Синтаксис оператора UNION

Additional languages

• English
• French
• German
• Japanese
• Portuguese

Buy this Reference Manual in softcover from Barnes & Noble!

Additional formats

• HTML, one page per chapter, tarball
• HTML, all on one page, tarball
• HTML, all on one page, ZIP

User Comments

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

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          |
+----+---------------+

+--------+------------+
| act_id | name       |
+--------+------------+
|      1 | Activity 1 |
|      2 | Activity 2 |
+--------+------------+

+------------+--------+-------+
| student_id | act_id | score |
+------------+--------+-------+
|          1 |      1 |    10 |
|          1 |      2 |    10 |
+------------+--------+-------+

+---------------+------------+------------+-------+
| codename      | Activity 1 | Activity 2 | Total |
+---------------+------------+------------+-------+
|          budy |         10 |         10 |    20 |
+---------------+------------+------------+-------+

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

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

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, "%")

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"

Below is an example of how to left-join multiple tables independently.

SELECT ...
FROM table 1
LEFT JOIN table 2 on (table1.id = table2.id)
LEFT JOIN table 3 on (table1.id2 = table3.id2)
LEFT JOIN table 4 on (table1.id3 = table4.id3)

Martin,
your comment & others helped emensely!
Here's a left-join select that also has a regular join to another table.
I want to get all Plans (& associated SubType info),
but also see which plans user 13 is signed up
for, but only if the expire_date hasn't passed.
This select will show all Plan & SubType info,
but user-info only if the user is signed up,
and the expire-date hasn't passed.

SELECT
*
FROM mt_SubTypes, mt_Plans as t1
LEFT JOIN mt_UserPlans as t2
on (t1.id_plan = t2.id_plan
and t2.expire_date > '2003-11-12'
and t2.id_user = 13)
WHERE
t1.id_subType = mt_SubTypes.id_subType;

Is there a way to run either a LEFT JOIN or an INNER JOIN on a database table that references another table twice in one record? Such as:
Table1

| T1_id | piece of info |
|  1    |    a
|  2    |    b

| T2_id | id1-T1 | id2-T1 |
|   33  |   1    |   2    |

|  33   |   a   |   b   |

This is an example of using a left to get lookup values from a table twice. The reason that an outer join was used instead of an inner join, was that in this case, there may be values that are null inside of the degree table.

SELECT d.degDegId, m1.majDescription AS major_1, m2.majDescription AS major_2
FROM degree AS d
LEFT OUTER JOIN major AS m1
ON d.degMajor1 = m1.majMajId
LEFT OUTER JOIN major AS m2
ON d.degMajor2 = m2.majMajId

For my first attempt at LEFT OUTER JOIN in MySQL, I foolishly followed the advice in the documentation:

"You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule."

A list of these exceptions would be helpful,
but for LEFT OUTER JOIN, I found the following:

Conditions for the "right table" go in the ON clause.
Conditions for the "left table" go in the WHERE clause,
except for the joining conditions themselves.

Until I discovered this, the result contained only the matching rows, or all rows from the left table, ignoring the conditions stated for filtering it.

Add your own comment.