mysql几种连接的比较

-- In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other).
-- In standard SQL, they are not equivalent.
-- INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
select t1.*,t2.* from student t1,score t2 where t1.s_id = t2.s_id;
-- cross join是笛卡尔积，理论上不应该支持on语法，但是MySQL在这方面做的比较奇怪。
select t1.*,t2.* from student t1 cross join score t2 on t1.s_id = t2.s_id;
select t1.*,t2.* from student t1 inner join score t2 on t1.s_id = t2.s_id;

-- 外连接必须加on条件
-- 左连接，取左边的表的全部，右边的表按条件，符合的显示，不符合则显示null
select t1.*,t2.* from student t1 left join score t2 on t1.s_id = t2.s_id;

-- 右连接：取右边的表的全部，左边的表按条件，符合的显示，不符合则显示null
select t1.*,t2.* from student t1 right join score t2 on t1.s_id = t2.s_id;

– FULL [OUTER] JOIN 产生A和B的并集。对于没有匹配的记录，则会以null做为值。mysql 不支持 full join

-- UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
-- union 会合并相同的行但合并速度慢一般不建议，union all 会保留相同的字段
select t1.* from student t1 where t1.s_id = '01' union all select t2.* from student t2

– sql 关键字执行顺序 from\on\join\where\group by\ having\select\distinct\union\order by