1. INNER JOIN与JOIN是相同
2. NATURAL JOIN
相同名称属性的值相等,只会出现一次,同名的属性不能超过一个,同名属性类型相同
SELECT * FROM Prisoner NATURAL JOIN Imprisoned
与上面等价,顺序可以互换,相当于交集 INNER JOIN
SELECT * FROM Imprisoned NATURAL JOIN Prisoner
可以选择列,添加条件WHERE
SELECT Age,Gender FROM Imprisoned NATURAL JOIN Prisoner WHERE ....
三个表的交集,顺序可以互换
SELECT * FROM Prison NATURAL JOIN Imprisoned NATURAL JOIN Prisoner
3. ON
MySQL中,NATURAL JOIN不能使用on指定其他查询条件
表1:tab2
id size
1 10
2 20
3 30
表2:tab2
size name
10 AAA
20 BBB
20 CCC
select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)
where再过滤
1 10 10 AAA
select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)(条件不为真也会返回左表中的记录)
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)
网友评论