SQL 关于自连接的连接条件问题
SQLZOO的self join问题:
5. Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road。
答案:
SELECT a.company, a.num, a.stop, b.stop
FROM route a
JOIN route b ON (a.company = b.company AND a.num = b.num)
WHERE a.stop = 53 AND b.stop = 149
不明白的点:(a.company = b.company AND a.num = b.num)
各位大佬们,小女子最近在自学sql,然后在做这个练习题的时候不太明白这道题自连接为什么条件是a.company = b.company AND a.num = b.num,因为我之前学的时候好像都是连接条件都是1个的,现在这里放两个条件是要去重的作用吗?
按我自己的理解是,这里self join的连接条件如果替换成 a.company = b.company,就会出现一个笛卡尔乘积,会出现很多一样的行如果连接条件是 a.company = b.company AND a.num = b.num,就要company和num都一样,可以把一样的行去掉?
我的表述有段乱,sorry~有大神可以帮忙解惑一下吗?非常感谢!