[생활코딩] SQL Join

opentutorials.org/course/3884

SQL Join - 생활코딩

(1) 표 쪼개기 (2) 실습 준비

(3) LEFT JOIN

SELECT tid, topic.title, author_id, name, profile.title AS job_title  

FROM topic LEFT JOIN author  # A = topic , B = author

ON topic.author_id = author.aid  # join 할 키 연결

LEFT JOIN profile  # A = 앞에서 만들었던 테이블 , B = profile 

ON author.profile_id = profile.pid

WHERE aid = 1;  # 조건 = 작가id가 1인 행

 

(4) INNER JOIN

SELECT *

FROM topic INNER JOIN author

ON topic.author_id = author.id

INNER JOIN profile

ON profile.pid = author.profile_id

 

(5) FULL OUTER JOIN

SELECT *

FROM topic FULL OUTER JOIN author

ON topic.author_id = author.id ;

 

(SELECT *

FROM topic LEFT JOIN author

ON topic.author_id = author.id)

UNION

(SELECT *

FROM topic RIGHT JOIN author

ON topic.author_id = author.id)

 

(6) EXCLUSIVE LEFT JOIN

SELECT *

FROM topic LEFT JOIN author

ON topic.author_id = author.aid

WHERE author.aid is NULL