https://opentutorials.org/module/4118

📔설명

RDB의 JOIN을 배워보자

---

📹표 쪼개기

노란색 배경이 중복이 발생하고 있다.
만약 city가 변경되어야 한다면 여러곳의 데이터를 다 바꿔버려야 하는 문제가 생긴다.

모든 테이블의 중복을 제거하면 위와 같은 테이블로 변하게 된다.

https://sql-joins.leopard.in.ua/

drop table author;
CREATE TABLE author (
  aid number NOT NULL,
  name varchar(10) DEFAULT NULL,
  city varchar(10) DEFAULT NULL,
  profile_id number DEFAULT NULL,
  PRIMARY KEY (aid)
);

INSERT INTO author VALUES (1,'egoing','seoul',1);
insert into author values(2,'leezche','jeju',2);
insert into author values(3,'blackdew','namhae',3);

DROP TABLE profile;
CREATE TABLE profile (
  pid number NOT NULL,
  title varchar(10) DEFAULT NULL,
  description varchar2(500),
  PRIMARY KEY (pid)
);
INSERT INTO profile VALUES (1,'developer','developer is ...');
INSERT INTO profile VALUES (2,'designer','designer is ..');
INSERT INTO profile VALUES (3,'DBA','DBA is ...');

DROP TABLE  topic;
CREATE TABLE topic (
  tid number NOT NULL,
  title varchar(45) DEFAULT NULL,
  description varchar2(500),
  author_id varchar(45) DEFAULT NULL,
  PRIMARY KEY (tid)
);

INSERT INTO topic VALUES (1,'HTML','HTML is ...','1');
INSERT INTO topic VALUES(2,'CSS','CSS is ...','2');
INSERT INTO topic VALUES (3,'JavaScript','JavaScript is ..','1');
INSERT INTO topic VALUES (4,'Database','Database is ...',NULL);

실습을 위해 db에 sql코드를 작성해두자!
oracle용으로 다 바꿨다!

---

👈🏻LEFT JOIN

A에 있는 정보는 물론, B에는 없는 A 정보도 필요할 때 사용

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

양쪽 테이블 모두에만 존재하는 것을 모은다.
null행 존재하지 않는다!

select * from topic inner join author on topic.author_id=author.aid;

select * from topic
inner join author on topic.author_id=author.aid
inner join profile on profile.pid=author.profile_id;	

---

🤝🏻FULL JOIN

왼쪽 오른쪽 모든 행을 출력한다 (UNION)

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

--아래랑 같다.
/*(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);*/

---

🤜🏻EXCLUSIVE JOIN

A입장에서 B와 연관되는 모든걸 배제하는 것이다.

select * from topic
left join author on topic.author_id=author.aid
where author.aid is null;

---

🕐이후

Docker 공부 이후 AWS로 넘어간다