[SQL] SQL JOIN 연습하기

* 코드에 의한 출력 결과는 시트 참고

 

Google Sheets: 로그인

1. 테이블 생성 코드

CREATE TABLE `author` (
  `aid` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `profile_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) 
INSERT INTO `author` VALUES (1,'egoing','seoul',1),(2,'leezche','jeju',2),(3,'blackdew','namhae',3);

CREATE TABLE `profile` (
  `pid` int(11) NOT NULL,
  `title` varchar(10) DEFAULT NULL,
  `description` tinytext,
  PRIMARY KEY (`pid`)
) 
INSERT INTO `profile` VALUES (1,'developer','developer is ...'),(2,'designer','designer is ..'),(3,'DBA','DBA is ...');

CREATE TABLE `topic` (
  `tid` int(11) NOT NULL,
  `title` varchar(45) DEFAULT NULL,
  `description` tinytext,
  `author_id` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`tid`)
)
INSERT INTO `topic` VALUES (1,'HTML','HTML is ...','1'),(2,'CSS','CSS is ...','2'),(3,'JavaScript','JavaScript is ..','1'),(4,'Database','Database is ...',NULL);

2. 조인 코드

- LEFT JOIN

SELECT * FROM topic 
SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.aid
SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.aid LEFT JOIN profile ON author.profile_id = profile.pid;
SELECT tid, topic.title, author_id, name, profile.title AS job_title FROM topic LEFT JOIN author ON topic.author_id = author.aid LEFT JOIN profile ON author.profile_id = profile.pid;
SELECT tid, topic.title, author_id, name, profile.title AS job_title FROM topic LEFT JOIN author ON topic.author_id = author.aid LEFT JOIN profile ON author.profile_id = profile.pid WHERE aid = 1;

- INNER JOIN

SELECT * FROM topic INNER JOIN author ON topic.author_id = author.aid
SELECT * FROM topic INNER JOIN author ON topic.author_id = author.id INNER JOIN profile ON profile.pid = author.profile_id

- FULL OUTER JOIN

SELECT * FROM topic FULL OUTER JOIN author ON topic.author_id = author.id

=

(SELECT * FROM topic LEFT OUTER JOIN author ON topic.author_id = author.id) UNION(SELECT * FROM topic RIGHT OUTER JOIN author ON topic.author_id = author.id)

UNION ALL의 경우 중복값 모두 표시한다.

 

- EXCLUSIVE JOIN

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.aid WHERE author.aid is NULL