MYSQL笛卡尔积连接/交叉连接

这两天在刷mysql题目的时候

遇到了需要将两张表进行笛卡尔连接的题目

如上图，需要将表Students与表Subjects进行笛卡尔连接

我在网上查询

只看到了这种方法

select *
from Students,Subjects

但是这种方法不能同时连接3张表

于是我采用了下面的方式

with a as (select *
from Students,Subjects)
select a.student_id,a.student_name,a.subject_name,count(Examinations.subject_name) as attended_exams 
from a
left join Examinations
on a.student_id = Examinations.student_id and a.subject_name = Examinations.subject_name
group by a.student_id,a.subject_name
order by a.student_id,a.subject_name

这样虽然通过了，但是怪怪的啊！！！！

然后我就去看了别人的解法

才发现

笛卡尔积可以这样连接！！！

SELECT 
    *
FROM
    Students
CROSS JOIN
    Subjects

可恶啊！！！！学到了！！！！

这样就能一起啦！

select Students.student_id,Students.student_name,Subjects.subject_name,count(Examinations.subject_name) as attended_exams 
from Students cross join Subjects
left join Examinations
on Students.student_id = Examinations.student_id and Subjects.subject_name = Examinations.subject_name
group by Students.student_id,Subjects.subject_name
order by Students.student_id,Subjects.subject_name