[TIL]SQLD: SQL 기본 문법 2(JOIN, 표준조인)+ Speak + 매카시 논문

9. SQL 기본 문법 2

--9-1. JOIN: 두 개 이상의 테이블을 연결&결합하여 데이터 출력 → 관계형 데이터베이스의 꽃!
-- 1)EQUI JOIN: 두 테이블 간 칼럼의 값들이 서로 같은 경우 두 데이터를 하나의 데이터로 합치는 JOIN
--  ①기본 구조: JOIN 할 두 테이블을 FROM 절에 나열 → 조건은 WHERE 절에 = 연산자로 표현_조건은 FROM절 테이블 N-1개 이상 필요

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... 
FROM 테이블1, 테이블2 
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2; --> 테이블1,2에 동일 컬럼 있을 수 있으므로  

--  연습문제)
-- customers 테이블 생성
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

-- orders 테이블 생성
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- customers 테이블에 데이터 삽입
INSERT INTO customers (customer_id, name, email) VALUES (101, 'John Doe', 'john.doe@example.com');
INSERT INTO customers (customer_id, name, email) VALUES (102, 'Jane Smith', 'jane.smith@example.com');
INSERT INTO customers (customer_id, name, email) VALUES (103, 'Tom Brown', 'tom.brown@example.com');

-- orders 테이블에 데이터 삽입
INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES (1, 101, DATE '2024-01-01', 150.00);
INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES (2, 102, DATE '2024-01-02', 200.00);
INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES (3, 101, DATE '2024-01-03', 50.00);
INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES (4, 103, DATE '2024-01-04', 300.00);
INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES (5, 102, DATE '2024-01-05', 100.00);

SELECT c.name, o.amount
FROM customers c, orders o
WHERE c.customer_id = o.customer_id AND o.amount >= 100
ORDER BY o.amount DESC;

--  2) Non EQUI JOIN: 칼럼들이 서로 정확하게 일치하는 것 x, 특정 범위 내에 있는 경우 JOIN → = 연산자가 아닌 ( BETWEEN , > , >= , < , <= ) 등 사용
--  ex)
SELECT e.ename '사원명', e.sal '급여', s.grade '급여등급'
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

--  !Cartesian Product (카티션 곱): JOIN 조건이 정의되지 않았거나  잘못된 경우, 또는 하나의 테이블이 다른 테이블의 모든 행과 JOIN이 된다면 모든 행의 조합이 나오게 된다. 

--  3) 3개 이상 TABLE JOIN: FROM절에 첫  번째와 두 번째 순서의 테이블이 먼저 JOIN 이 완료되고 그다음 순서로 JOIN
--  ex)
-- nation 테이블 생성
CREATE TABLE nation (
    nation_id INT PRIMARY KEY,
    nation_name VARCHAR(50)
);

-- nation 테이블에 데이터 삽입
INSERT INTO nation (nation_id, nation_name) VALUES (1, 'USA');
INSERT INTO nation (nation_id, nation_name) VALUES (2, 'Canada');
INSERT INTO nation (nation_id, nation_name) VALUES (3, 'UK');
INSERT INTO nation (nation_id, nation_name) VALUES (4, 'Australia');
INSERT INTO nation (nation_id, nation_name) VALUES (5, 'Germany');

-- sport 테이블 생성
CREATE TABLE sport (
    sport_id INT PRIMARY KEY,
    sport_name VARCHAR(50)
);

-- sport 테이블에 데이터 삽입
INSERT INTO sport (sport_id, sport_name) VALUES (1, 'Basketball');
INSERT INTO sport (sport_id, sport_name) VALUES (2, 'Soccer');
INSERT INTO sport (sport_id, sport_name) VALUES (3, 'Swimming');
INSERT INTO sport (sport_id, sport_name) VALUES (4, 'Tennis');
INSERT INTO sport (sport_id, sport_name) VALUES (5, 'Athletics');

-- participant 테이블 생성
CREATE TABLE participant (
    participant_id INT PRIMARY KEY,
    participant_name VARCHAR(50),
    nation_id INT,
    sport_id INT,
    FOREIGN KEY (nation_id) REFERENCES nation(nation_id),
    FOREIGN KEY (sport_id) REFERENCES sport(sport_id)
);

-- participant 테이블에 데이터 삽입
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (1, 'Michael Jordan', 1, 1);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (2, 'Lionel Messi', 2, 2);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (3, 'Michael Phelps', 1, 3);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (4, 'Serena Williams', 4, 4);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (5, 'Usain Bolt', 3, 5);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (6, 'Roger Federer', 4, 4);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (7, 'Katie Ledecky', 1, 3);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (8, 'Neymar Jr', 2, 2);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (9, 'Jessica Ennis', 5, 5);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (10, 'Mo Farah', 3, 5);
-- nation 테이블 생성
CREATE TABLE nation (
    nation_id INT PRIMARY KEY,
    nation_name VARCHAR(50)
);

-- nation 테이블에 데이터 삽입
INSERT INTO nation (nation_id, nation_name) VALUES (1, 'USA');
INSERT INTO nation (nation_id, nation_name) VALUES (2, 'Canada');
INSERT INTO nation (nation_id, nation_name) VALUES (3, 'UK');
INSERT INTO nation (nation_id, nation_name) VALUES (4, 'Australia');
INSERT INTO nation (nation_id, nation_name) VALUES (5, 'Germany');

-- sport 테이블 생성
CREATE TABLE sport (
    sport_id INT PRIMARY KEY,
    sport_name VARCHAR(50)
);

-- sport 테이블에 데이터 삽입
INSERT INTO sport (sport_id, sport_name) VALUES (1, 'Basketball');
INSERT INTO sport (sport_id, sport_name) VALUES (2, 'Soccer');
INSERT INTO sport (sport_id, sport_name) VALUES (3, 'Swimming');
INSERT INTO sport (sport_id, sport_name) VALUES (4, 'Tennis');
INSERT INTO sport (sport_id, sport_name) VALUES (5, 'Athletics');

-- participant 테이블 생성
CREATE TABLE participant (
    participant_id INT PRIMARY KEY,
    participant_name VARCHAR(50),
    nation_id INT,
    sport_id INT,
    FOREIGN KEY (nation_id) REFERENCES nation(nation_id),
    FOREIGN KEY (sport_id) REFERENCES sport(sport_id)
);

-- participant 테이블에 데이터 삽입
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (1, 'Michael Jordan', 1, 1);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (2, 'Lionel Messi', 2, 2);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (3, 'Michael Phelps', 1, 3);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (4, 'Serena Williams', 4, 4);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (5, 'Usain Bolt', 3, 5);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (6, 'Roger Federer', 4, 4);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (7, 'Katie Ledecky', 1, 3);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (8, 'Neymar Jr', 2, 2);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (9, 'Jessica Ennis', 5, 5);
INSERT INTO participant (participant_id, participant_name, nation_id, sport_id) VALUES (10, 'Mo Farah', 3, 5);

--8-2. 표준 조인
-- 1) STANDARD SQL
--  ①일반 집합 연산자
--   •UNION(합집합) / UNION ALL(공통 집합을 중복해서 그대로 보여줌, 효율성 높음)
--   •INTERSECTION(교집합) / DIFFERENCE(차집합) / PRODUCT(곱집합=CARTESIAN PRODUCT)

--  ②순수 관계 연산자
--   •SELECT(WHERE절 처럼 특정 행에 대한 부분 집합이) / PROJECT(특정 열에 대한 부분 집합)
--   •JOIN(INNER JOIN, NATURAL JOIN, OUTER JOIN 등)

-- 2) 대표적인 JOIN의 6가지 방식
--   ①INNER JOIN: 교집합 행만 반환 = EQUI 조인
--   ②NATURAL JOIN: 두 테이블 간 동일한 이름을 갖는 모든 칼럼에 대해 INNER JOIN 수행
--   ③USING 조건절: 같은 이름을 가진 칼럼에서 원하는 칼럼만 선택적으로 INNER JOIN
--   ④ON 조건절: 명시적으로 JOIN 조건을 지정 시 사용
--   ⑤CROSS JOIN: PRODUCT의 개념
--   ⑥OUTER JOIN: INNER JOIN과 달리 JOIN 조건에서 동일한 값이 없는 행도 결과 집합에 포함

--   ①INNER JOIN
SELECT 테이블1.칼럼1, 테이블2.칼럼2, ..
FROM 테이블1 [INNER] JOIN 테이블2 
ON 테이블1.칼럼 = 테이블2.칼럼;

--   ②NATURAL JOIN
SELECT 칼럼1, 칼럼2, ..
FROM 테이블1 NATURAL JOIN 테이블2; --> on절 필요x

--   ③USING 조건절
SELECT 칼럼1, 칼럼2, .. -- join 컬럼에 Alias 사용 불가
FROM 테이블1 JOIN 테이블2 
	USING (EXPR); -- on보다 간편. 괄호 없으면 애러 발생

--   ④ON 조건절 → ALIAS나 테이블명을 접두사로 붙여도 상관없으며 오히려 논리적으로 명확 하게 지정해 주는것이 좋다
--    •WHERE과 혼용 가능
SELECT e.ename, e.deptno, d.deptno, d.dname
FROM emp e JOIN dept d 
	ON e.deptno = d.deptno
WHERE e.deptno = 30;
--    •ON 조건절 + 데이터 검증 조건 추가
--    •다중 테이블 JOIN
SELECT e.empno, d.deptno, d.dname, t.dname AS new_dname 
FROM emp e JOIN dept d 
    ON e.deptno = d.deptno JOIN dept_temp t  
    ON e.deptno = t.deptno;
--   ⑥OUTER JOIN: 기준 테이블을 잡아 모두 보여주고, 다른 테이블은 교집합 시 보여준다. 
--    •LEFT/RIGHT OUTER JOIN
--     ex)
CREATE TABLE VolunteerActivity (
    activity_id NUMBER PRIMARY KEY,
    juice_type VARCHAR2(20),
    quantity_sold NUMBER
);

INSERT INTO VolunteerActivity VALUES (1, 'Orange Juice', 100);
INSERT INTO VolunteerActivity VALUES (2, 'Apple Juice', 80);
INSERT INTO VolunteerActivity VALUES (3, 'Grape Juice', 640);
INSERT INTO VolunteerActivity VALUES (4, 'Pineapple Juice', 300);
INSERT INTO VolunteerActivity VALUES (5, 'Lemonade', 110);

CREATE TABLE Unit (
    unit_id NUMBER PRIMARY KEY,
    unit_name VARCHAR2(20),
    activity_id NUMBER,
    FOREIGN KEY (activity_id) REFERENCES VolunteerActivity(activity_id)
);

INSERT INTO Unit VALUES (101, 'Alpha Unit', 1);
INSERT INTO Unit VALUES (102, 'Bravo Unit', 2);
INSERT INTO Unit VALUES (103, 'Charlie Unit', 3);
INSERT INTO Unit VALUES (104, 'Delta Unit', 4);
INSERT INTO Unit VALUES (105, 'Echo Unit', 5);
INSERT INTO Unit VALUES (106, 'Grenadier Unit', 5);
INSERT INTO Unit VALUES (107, 'Parachute Unit', 4);

CREATE TABLE Soldier (
    soldier_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(20),
    rank VARCHAR2(20),
    unit_id NUMBER,
    activity_id NUMBER,
    FOREIGN KEY (unit_id) REFERENCES Unit(unit_id),
    FOREIGN KEY (activity_id) REFERENCES VolunteerActivity(activity_id)
);

INSERT INTO Soldier VALUES (3028, 'John', 'Doe', 'Sergeant', 101, 1);
INSERT INTO Soldier VALUES (2734, 'Jane', 'Smith', 'Private', 102, 2);
INSERT INTO Soldier VALUES (3103, 'Michael', 'Johnson', 'Private', 103, 3);
INSERT INTO Soldier VALUES (4865, 'Emily', 'Davis', 'Private', 104, 4);
INSERT INTO Soldier VALUES (5371, 'Chris', 'Brown', 'Sergeant', 105, 5);
INSERT INTO Soldier VALUES (0297, 'Eleanor', 'Grace', 'Sergeant',NULL, NULL);
INSERT INTO Soldier VALUES (9234, 'Alexander', 'James', 'Sergeant',NULL, NULL); 
```
SELECT U.unit_id
     , U.unit_name
     , S.first_name
FROM Unit U LEFT OUTER JOIN Soldier S
ON (U.unit_id = S.unit_id)
ORDER BY unit_id
;

--    •FULL OUTER JOIN: LEFT OUTER JOIN과  RIGHT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일(공통+LEFT+RIGHT)