SQL 명령어 서브 쿼리, 테이블 조인 구문

서브 쿼리
SELECT title, type, price FROM titles WHERE title_id IN (
    SELECT title_id  FROM titleauthor WHERE au_id = '998-72-3567')
SELECT title_id FROM titles WHERE title_id IN (
    SELECT title_id FROM sales)

테이블 조인

- 내부 조인 (교집합 형태). 일반적으로 많이 쓰임.
SELECT t.title, t.price, p.pub_name, p.city, p.state 
FROM titles AS t, publishers AS p 
WHERE t.pub_id = p.pub_id

- 내부 조인의 표준 SQL 문장
SELECT t.title, t.price, p.pub_name, p.city, p.state 
FROM titles AS t INNER JOIN publishers AS p 
ON t.pub_id = p.pub_id 
SELECT a.city, p.pub_name 
FROME authors AS a INNER JOIN publishers AS p
ON a.city = p.city

- 외부 조인(합집합 형태). 왼쪽 테이블 기준
SELECT t.pub_id, p.pub_id, t.title, t.price, p.pub_name, p.city, p.state
FROM titles AS t LEFT OUTER JOIN publishers AS p
ON t.pub_id = p.pub_id 

- 외부 조인. 오른쪽 테이블 기준
SELECT t.pub_id, p.pub_id, t.title, t.price, p.pub_name, p.city, p.state
FROM titles AS t RIGHT OUTER JOIN publishers AS p
ON t.pub_id = p.pub_id