【MySQL学习】JOIN ON详解

JOIN在MySQL中出现非常频繁，经常用于多表关联查询。

1.三种形式JOIN的sql语句示例

/*LEFT JOIN
* 左关联，此时a表为主表，a、b关联后的临时表中a表所有数据均存在*/
SELECT * FROM a LEFT JOIN b ON a.id = b.id

/*RIGHT JOIN
* 右关联，此时b表为主表，a、b关联后的临时表中b表所有数据均存在*/
SELECT * FROM a RIGHT JOIN b ON a.id = b.id

/*INNER JOIN == JOIN
* 等值关联，返回两个表中关联字段相等的数据行*/
SELECT * FROM a INNER JOIN b ON a.id = b.id

2.ON的作用

ON用来生成关联临时表，生成的临时表会将主表的所有记录保存下来，等待WHERE语句的进一步筛选。

3.多条JOIN语句的含义及示例

两表以上关联时需要用到多条JOIN语句，一般用LEFT JOIN确立唯一的主表

SELECT * FROM t
LEFT JOIN a on t.tender_no = a.tender_no AND t.company_id = a.company_id
LEFT JOIN tb on t.tender_no = tb.tender_no AND t.company_id = tb.company_id
LEFT JOIN c on tb.bidder_company_id = c.company_id AND tb.company_id = c.plate_company_id
LEFT JOIN cb on tb.bidder_company_id = cb.company_id
LEFT JOIN b on b.bidder_company_id = tb.bidder_company_id AND b.company_id = tb.company_id AND b.aggregate_no = a.aggregate_no AND b.company_id = a.company_id
WHERE t.company_id = #{companyId}

参考网址：https://blog.csdn.net/wqc19920906/article/details/79785424