SQL JOIN 关联条件和 where 条件的异同

Inner join

对于 inner join，条件写到 on 和 where 部分是一样的。

select count(1) from web_site join web_page on web_site_sk=wp_web_page_id where web_city='Pleasant Hill' and wp_access_date_sk=1;

输出

0

select count(1)from web_site join web_page on web_site_sk=wp_web_page_id and web_city='Pleasant Hill' and wp_access_date_sk=1;

输出

0

Outer join

对于 outer join 写到 on 和 where 部分结果是不一样的。
写到 where 部分代表着 join 结果的过滤。

select count(1) from web_site left outer join web_page on web_site_sk=wp_web_page_id where web_city='Pleasant Hill' and wp_access_date_sk=1;

输出

0

left outer join on 条件是关联条件，对于 left outer join，关联不上，左边的数据也要输出。

select count(1)from web_site left outer join web_page on web_site_sk=wp_web_page_id and web_city='Pleasant Hill' and wp_access_date_sk=1;

输出

32