DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`
(
    `c_id`   int          DEFAULT NULL COMMENT '班級ID',
    `c_name` varchar(50) DEFAULT NULL COMMENT '班級名'
);
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
    `s_id`   int          DEFAULT NULL COMMENT '學生ID',
    `s_name` varchar(50) DEFAULT NULL COMMENT '學生名',
    `c_id`   int          DEFAULT NULL COMMENT '班級ID'
);
INSERT INTO `class` (`c_id`, `c_name`)
VALUES (1, '一班'),
       (2, '二班'),
       (3, '三班');
INSERT INTO `student` (`s_id`, `s_name`, `c_id`)
VALUES (1, '張三', 1),
       (2, '李四', 2),
       (3, '王五', 4);

-- 單表
select * from class c;
select * from student s;
-- 笛卡爾積
select * from class c inner join student s 										order by c.c_id, s.s_id;
-- 內連
select * from class c inner join student s on c.c_id = s.c_id order by c.c_id, s.s_id;
-- 左外連（先on，再join，再where）
select * from class c left  join student s on c.c_id = s.c_id 									order by c.c_id, s.s_id;
select * from class c left  join student s on c.c_id = s.c_id and   c.c_id <> 2	order by c.c_id, s.s_id;
select * from class c left  join student s on c.c_id = s.c_id where c.c_id <> 2	order by c.c_id, s.s_id;
select * from class c left  join student s on c.c_id = s.c_id and   s.c_id <> 2	order by c.c_id, s.s_id;
select * from class c left  join student s on c.c_id = s.c_id where s.c_id <> 2	order by c.c_id, s.s_id;