用一条SQL查询一张表中不同级别的字段(字段名称相同)。
需求：用一条SQL同时查出城市id、城市name、省份id和省份name。
版本一如下
SELECT
tlUp.LOCATION_ID as cityId,
tlUp.LOCATION_NAME as cityName,
tlDown.LOCATION_ID as provinceId,
tlDown.LOCATION_NAME as provinceName
FROM
(SELECT
tl.LOCATION_ID,
tl.LOCATION_NAME,
tl.SUPER_LOCATION_ID
FROM
t_location tl) tlUp,t_location tlDown
WHERE
tlUp.SUPER_LOCATION_ID = tlDown.LOCATION_ID
版本二
SELECT
tlOne.LOCATION_ID as cityId,
tlOne.LOCATION_NAME as cityName,
tlTwo.LOCATION_ID as provinceId,
tlTwo.LOCATION_NAME as provinceName
FROM
t_location tlOne
LEFT JOIN
t_location tlTwo
ON
tlOne.SUPER_LOCATION_ID = tlTwo.LOCATION_ID
版本二查询出来的结果
SQL版本三
SELECT
tlOne.LOCATION_ID as cityId,
tlOne.LOCATION_NAME as cityName,
tlTwo.LOCATION_ID as provinceId,
tlTwo.LOCATION_NAME as provinceName
FROM
t_location tlOne
LEFT JOIN
t_location tlTwo
ON
tlOne.SUPER_LOCATION_ID = tlTwo.LOCATION_ID
AND
tlOne.LOCATION_LEVEL = '2'
查询出的结果同版本二一样
别人写的SQL版本一如下
SELECT
tlOne.LOCATION_ID as cityId,
tlOne.LOCATION_NAME as cityName,
tlTwo.LOCATION_ID as provinceId,
tlTwo.LOCATION_NAME as provinceName
FROM
t_location tlOne
LEFT JOIN
t_location tlTwo
ON
tlOne.SUPER_LOCATION_ID = tlTwo.LOCATION_ID
WHERE
tlOne.LOCATION_LEVEL = '2'
查询出的结果同版本一 一样
别人写的SQL版本二如下
SELECT
tlUp.LOCATION_ID as cityId,
tlUp.LOCATION_NAME as cityName,
tlDown.LOCATION_ID as provinceId,
tlDown.LOCATION_NAME as provinceName
FROM t_location tlUp,t_location tlDown
WHERE
tlUp.SUPER_LOCATION_ID = tlDown.LOCATION_ID
仔细观察上述SQL会发现
我写的版本三和别人写的版本一唯一的区别在于on后面的条件连接关键词，我用的是ON，别人用的是where。这里延伸出一个问题:
left join on后面跟and和where的区别
1.on后面and条件表示先过滤之后，再连接
2.on后面where条件表示先关联之后，再过滤 相当内连了。。