实例讲解MySQL联
表A:
ID | Name |
1 | Tim |
2 | Jimmy |
3 | John |
4 | Tom |
表B:
ID | Hobby |
1 | Football |
2 | Basketball |
2 | Tennis |
4 | Soccer |
Select A.Name B.Hobby from A, B where A.id = B.id,这是隐式的
Name | Hobby |
Tim | Football |
Jimmy | Basketball |
Jimmy | Tennis |
Tom | Soccer |
它的作用和 Select A.Name from A INNER JOIN B ON A.id = B.id是一样的。这
2. 外左联结
Select A.Name from A Left JOIN B ON A.id = B.id,典型的外左
Name | Hobby |
Tim | Football |
Jimmy | Basketball |
John | |
Tom | Soccer |
所以从上面结果看出,
3. 外右联结
如果把上面查询改成外
Name | Hobby |
Tim | Football |
Jimmy | Basketball |
Jimmy | Tennis |
Tom | Soccer |
这样的结果都是我们可
说到这里大家是否对联
1.USING (column_li
a LEFT JOIN b USING (c1,c2,c3)
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
只是用ON来代替会书
2.NATURAL [LEFT] JOIN:这个句子的
3.STRAIGHT
最后要说的就是,在M
SELECT t1.id,t2.i
FROM t1,t2
LEFT JOIN t3 ON (t3.id=t1.
WHERE t1.id=t2.i
但是MySQL并不是
SELECT t1.id,t2.i
FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.
WHERE t1.id=t2.i
这并不是我们想要的效
SELECT t1.id,t2.i
FROM (t1,t2)
LEFT JOIN t3 ON (t3.id=t1.
WHERE t1.id=t2.i
在这里括号是相当重要