SQL join语句和实例图片

内链接

SELECT vend_name, prod_name, prod_price

FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id; ##内连接，inner join

自然连接

SELECT C.*, O.order_num,

O.order_date, OI.prod_id, OI.quantity, OI.item_price

FROM Customers AS C, Orders AS O, OrderItems AS OI

WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id ='RGAN01'; ##自然链接

内连接，采用on作为过滤条件；

SELECT * FROM

ods_study_1.ods_study_join_a A

Inner JOIN

ods_study_1.ods_study_join_b B

ON (A.study_id_a = B.study_id_b AND B.study_channel='weixin')

SELECT *

FROM  ods_study_1.ods_study_join_a A

Inner JOIN

ods_study_1.ods_study_join_b B

ON (A.study_id_a = B.study_id_b )

WHERE B.study_channel='weixin'；

采用内连接inner join，过滤条件放在on和where中返回结果一致。

采用左连接，观察 on和where对条件的筛选：

SELECT *

FROM

ods_study_1.ods_study_join_a A

LEFT JOIN

ods_study_1.ods_study_join_b B

ON (A.study_id_a = B.study_id_b AND B.study_channel='weixin')

SELECT  *

FROM ods_study_1.ods_study_join_a A

LEFT JOIN

ods_study_1.ods_study_join_b B

ON (A.study_id_a = B.study_id_b )

WHERE B.study_channel='weixin'

inner join 内连接，中间重叠的部分；左连接，以左边的表为主；右连接，需要以右边的表为主，自己判定是否包括inner join的内容。