sql_LEFT [OUTER] JOIN,RIGHT [OUTER] JOIN,FULL [OUTER] JOIN,CROSS JOIN,INNER JOIN_spark为什么left join 加了on 后还是报cross join问题-CSDN博客

本文链接：https://blog.csdn.net/icecoola_/article/details/79578896

本文详细介绍了SQL中不同类型的连接操作，包括交叉连接、内连接、左外连接、右外连接和全连接，并提供了具体的示例及结果对比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select count(1) from t_student  32239
select count(1) from t_class   1190

select count(1) from t_student  a  JOIN t_class b on a.shop_id = b.id     26941

====CROSS JOIN
 交叉连接CROSS JOIN (注：cross join后加条件只能用where,不能用on)
 如果不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积；
select count(1) from t_student  a  CROSS JOIN t_class b     38364410
select count(1) from t_student a, t_class b              38364410
select count(1) from t_student  a  CROSS JOIN t_class b WHERE a.shop_id = b.id  26941

====INNER JOIN
内链接,两种方式效果一样,查询的是两边都有的数据
select count(1) from t_student a, t_class b where a.shop_id = b.id   26941
select count(1) from t_student  a INNER JOIN t_class b on a.shop_id = b.id  26941

cross join是做笛卡尔积的，inner join是做等值连接的

====LEFT JOIN
左外连接，在内链接基础上，左边表有而右边表没有，两种方式等效；
select count(1) from t_student  a LEFT JOIN t_class b on a.shop_id = b.id    32239
select count(1) from t_student  a LEFT OUTER JOIN t_class b on a.shop_id = b.id  32239


====RIGHT JOIN
右外连接，在内链接基础上，右边有而左边无，两种方式等效；
select count(1) from t_student  a RIGHT JOIN t_class b on a.shop_id = b.id   27007
select count(1) from t_student  a RIGHT OUTER JOIN t_class b on a.shop_id = b.id   27007
select DISTINCT count(1) from t_class b LEFT JOIN t_student  a on a.shop_id = b.id  27007


====FULL JOIN
显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集
select count(1) from t_student  a FULL JOIN t_class b on a.shop_id = b.id   32305
select count(1) from t_student  a FULL  OUTER JOIN t_class b on a.shop_id = b.id   32305
select count(1) from t_class  a FULL JOIN t_student b on a.id = b.shop_id   32305

FULL JOIN = LEFT JOIN + RIGHT JOIN - INNER JOIN 
select (32239+27007-26941)  32305