codecamedy의 learn SQL 4. Multiple Tables

 

https://www.codecademy.com/learn/learn-sql

SQL Tutorial: Learn SQL For Free | Codecademy

 

1. Combining Tables with SQL

SELECT *
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id;

내가 보고싶은 주 테이블은 orders. 여기에 customers의 추가 정보를 붙이고 싶다. join을 한다.

 

여기에 where를 붙혀 어떤 조건을 만족하는지 join되는 테이블의 필터를 사용할 수도 있음.

SELECT *
FROM orders
JOIN subscriptions
  ON orders.subscription_id = subscriptions.subscription_id
WHERE subscriptions.description = 'Fashion Magazine';

 

 

on을 함으로써 덕지덕지 cross로 붙는 걸 제한하는 느낌 (실제로 on은 선택임)

2. Inner Joins

select count(*)
from newspaper
join online
on newspaper.id = online.id;

정보 누락 시 얄짤없이 사라짐

 

3. Left Joins

SELECT *
FROM table1
LEFT JOIN table2
  ON table1.c2 = table2.c2;

왼쪽 정보는 삭제되지 않고 그대로 남아있음.

 

 

4. Primary Key vs Foreign Key

primary key의 조건

• None of the values can be NULL.
• Each value must be unique (i.e., you can’t have two customers with the same customer_id in the customers table).
• A table can not have more than one primary key column.

위 표 예시는 order의 table. order의 primary key는id, customer table의 primary key는 id, subscription table의 primary key도.

자신의 table에서 자신이 primary key도 있지만, 다른 table의 primary key도 들어있을 수 있음. 이걸 foreign key라고 함. 즉 위 order table에서 customer_id는 비록 저기선 foreign key지만 얘도 customer table에 가면 자기가 primary key다.

 

select *
from classes
join students
on classes.id = students.class_id;

 

 

5. Cross Join

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

on에 상관없이 그냥 막 붙이는거. 원본 table 하나에 cross join할 table의 모든 row 붙이고, 다음 원본 table에 join table 모든 row 붙이고...

 

가끔 중요한 역할을 하는 듯 하다.

 

 

6. Union

 

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

 

걍 row로 두개 합치기. 때문에 조건이 필요하다.

 

• Tables must have the same number of columns.
• The columns must have the same data types in the same order as the first table.

걍 완전히 같아야 됨.

 

 

7. With

WITH previous_results AS (
   SELECT ...
   ...
   ...
   ...
)
SELECT *
FROM previous_results
JOIN customers
  ON _____ = _____;

그냥 query문 두번 이용하는거.

• The WITH statement allows us to perform a separate query (such as aggregating customer’s subscriptions)
• previous_results is the alias that we will use to reference any columns from the query inside of the WITH clause
• We can then go on to do whatever we want with this temporary table (such as join the temporary table with another table)

 

 

• JOIN will combine rows from different tables if the join condition is true.
• LEFT JOIN will return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table.
• Primary key is a column that serves a unique identifier for the rows in the table.
• Foreign key is a column that contains the primary key to another table.
• CROSS JOIN lets us combine all rows of one table with all rows of another table.
• UNION stacks one dataset on top of another.
• WITH allows us to define one or more temporary tables that can be used in the final query.