[SQL] Mutiple Tables

■ 코드카데미(codecademy) SQL 연습해보기

https://www.codecademy.com/courses/learn-sql/lessons/multiple-tables/exercises/generalizations-multiple-tables

■ Cheating Sheets

https://www.codecademy.com/learn/learn-sql/modules/learn-sql-multiple-tables/cheatsheet

LESSON 4 : MULTIPLE TABLES

---

JOIN (= Inner Join, 교집합)

SELECT *
FROM books
JOIN authors
    ON books.author_id = authors.id;

---

LEFT JOIN (= Outer Join)

SELECT column_name
FROM table1
LEFT JOIN table2
    ON = table1.column_name = table2.column_name;

---

Primary key, Foreign key

On first table, "customer_id" is primary key.

On second table, "order_id" is primary key, 'customer_id' is foreign key.

---

CROSS JOIN (= 경우의 수, 모든 조합)

SELECT shirts.shirt_color,
    pants.panths_color
FROM shirts
CROSS JOIN pants;

---

UNION

두개의 결과물을 세로로 합칠 수 있다.

열이 같아야 함

SELECT name
FROM first_names
UNION
SELECT name
FROM last_names

---

WITH

결과물을 괄호 안에 넣어 임시테이블(temporary table)을 만들어서 사용할 수 있다.

▶ WITH "임시이름" AS("결과물")

WITH temporary_movies AS(
    SELECT *
    FROM movies
)

SELECT *
FROM temporaty_movies
WHERE year BETWEEN 2000 AND 2020;