조인 정리

Scheme

CREATE TABLE races
(
  races_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200)
);

CREATE TABLE friends
(
  friends_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200),
  races_id BIGINT,
  CONSTRAINT character_races FOREIGN KEY(races_id) REFERENCES races(races_id)
);

INSERT INTO races(name) VALUES("동물");
INSERT INTO races(name) VALUES("과일");
INSERT INTO races(name) VALUES("사람");

INSERT INTO friends(name, races_id) VALUES("무지", null);
INSERT INTO friends(name, races_id) VALUES("라이언", 1);
INSERT INTO friends(name, races_id) VALUES("어피치", 2);

 

 

 

 

 

 

Query

# MySQL was not support outer join
SELECT * FROM friends INNER JOIN races ON friends.races_id=races.races_id;
SELECT * FROM friends LEFT JOIN races ON friends.races_id=races.races_id;
SELECT * FROM friends RIGHT JOIN races ON friends.races_id=races.races_id;

 

* MySQL 은 Outer join을 지원하지 않는다.

Outer join 을 흉내내고 싶다면 아래 링크와 Union query를 참조

https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql

SELECT * FROM friends LEFT JOIN races ON friends.races_id=races.races_id 
UNION
SELECT * FROM friends RIGHT JOIN races ON friends.races_id=races.races_id;

 

 

 

 

 

Outer join 결과

 

Inner join 결과

 

Left join 결과

 

Right join 결과

 

 

 

 

 

SQL test on web : http://sqlfiddle.com/

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.