Copy CREATE DATABASE join_db;
USE join_db;
CREATE TABLE course(
course_id varchar(20),
title varchar(20),
dept_name varchar(20),
credits int,
PRIMARY KEY (course_id)
);
INSERT INTO course values('CS-101', '๋ฐ์ดํฐ๋ฒ ์ด์ค', '์ปดํจํฐ๊ณตํ๋ถ', 3);
INSERT INTO course values('CS-103', '๋ฐ์ดํฐ์ฌ์ด์ธ์ค๊ฐ๋ก ', '์ปดํจํฐ๊ณตํ๋ถ', 3);
INSERT INTO course values('CS-105', '์๋ฃ๊ตฌ์กฐ', '์ปดํจํฐ๊ณตํ๋ถ', 3);
INSERT INTO course values('CS-201', '์๊ณ ๋ฆฌ์ฆ', '์ปดํจํฐ๊ณตํ๋ถ', 3);
INSERT INTO course values('CS-303', '์ปดํจํฐ๊ณตํ์ธ๋ฏธ๋', '์ปดํจํฐ๊ณตํ๋ถ', 1);
CREATE TABLE prereq(
course_id varchar(20),
prereq_id varchar(20),
PRIMARY KEY (course_id, prereq_id)
);
INSERT prereq values('CS-201', 'CS-101');
INSERT prereq values('CS-201', 'CS-105');
INSERT prereq values('CS-204', 'CS-105');
Copy SELECT c.course_id, title, dept_name, credits, p.prereq_id
FROM course c join prereq p on c.course_id = p.course_id;
Copy SELECT c.course_id, title, dept_name, credits, p.prereq_id
FROM course c LEFT JOIN prereq p on c.course_id = p.course_id
Copy SELECT c.course_id, title, dept_name, credits, p.prereq_id
FROM course c RIGHT JOIN prereq p on c.course_id = p.course_id;
Copy SELECT c.course_id, title, dept_name, credits, p.prereq_id
FROM course c LEFT JOIN prereq p on c.course_id = p.course_id
UNION
SELECT c.course_id, title, dept_name, credits, p.prereq_id
FROM course c RIGHT JOIN prereq p on c.course_id = p.course_id;
Copy SELECT c.course_id, title, dept_name, credits, p.prereq_id
FROM course c CROSS JOIN prereq p;
SELECT c.course_id, title, dept_name, credits, p.prereq_id
FROM course c, prereq p;
Copy SELECT *
FROM course c1, course c2;