mysql 多表查询

-- 分享主题：mysql多表查询-- 本库中表：act promotion item-- left join:    SELECT a.act_id,a.act_name,p.prmotion_desc FROM act a LEFT JOIN promotion p ON a.act_id = p.act_id;       -- 查询活动列表   SELECT a.act_id,a.act_name,SUM(IF(i.act_id IS NULL,0,1)) item_count FROM act a LEFT JOIN item i ON a.act_id = i.act_id GROUP BY act_id;       SELECT a.act_id,a.act_name,i.* FROM act a LEFT JOIN item i ON a.act_id = i.act_id;               EXPLAIN SELECT a.act_id,a.act_name,i.title FROM act a LEFT JOIN item i ON a.act_id = i.act_id  WHERE i.act_id IS NOT NULL ;-- right join   SELECT a.*,i.* FROM act a RIGHT JOIN item i ON a.act_id = i.act_id; -- inner join   SELECT a.*,i.* FROM act a LEFT JOIN item i ON a.act_id = i.act_id;   SELECT a.*,i.* FROM act a INNER JOIN item i ON a.act_id = i.act_id;        -- on a.c1 = b.c1 等同于 using(c1) 如果连接的两个表连接条件的两个列具有相同的名字   SELECT a.*,i.* FROM act a INNER JOIN item i USING (act_id); -- INNER JOIN 和 , (逗号) 在语义上是等同的   SELECT a.*,i.* FROM act a,item i ;   SELECT a.*,i.* FROM act a INNER JOIN item i ;   SELECT a.*,i.* FROM act a,item i WHERE a.act_id = i.act_id; -- 从左表读出一条，选出所有与on匹配的右表纪录(n条)进行连接，形成n条纪录(包括重复的行，-- 如：结果1和结果3)，如果右边没有与on条件匹配的表，那连接的字段都是null.然后继续读下一条