7일차에 배운 내용을 정리하였다.
목차는 다음과 같다. :
01 JOIN
-01 JOIN 실습(MOVIE2)
-02 OUTER JOIN
-03 SELF JOIN
02 JOIN의 두 가지 문법 (표준 조인)
-01 INNER JOIN
-02 OUTER JOIN
-03 NATURAL JOIN
-04 CROSS JOIN
-05 FULL OUTER JOIN
-05 USING 절
01 JOIN
-01 JOIN 실습(MOVIE2)
우선 실습용 테이블을 생성한다.
GENRE, ACTOR, MOVIE2 테이블 3개로 각각 영화장르, 배우 정보, 영화 정보 및 평점 등으로 구성되어 있다.
CREATE TABLE GENRE (
GENRE_ID NUMBER(3) CONSTRAINT PK_GENRE PRIMARY KEY,
GENRE_NAME VARCHAR2(50) CONSTRAINT UK_GENRE_NAME UNIQUE
);
INSERT INTO GENRE VALUES (1, '액션');
INSERT INTO GENRE VALUES (2, '드라마');
INSERT INTO GENRE VALUES (3, '코미디');
INSERT INTO GENRE VALUES (4, '스릴러');
INSERT INTO GENRE VALUES (5, '범죄');
INSERT INTO GENRE VALUES (6, '로맨스');
INSERT INTO GENRE VALUES (7, 'SF');
INSERT INTO GENRE VALUES (8, '판타지');
INSERT INTO GENRE VALUES (9, '애니메이션');
INSERT INTO GENRE VALUES (10, '미스터리');
COMMIT;
CREATE TABLE ACTOR (
ACTOR_ID NUMBER(4) CONSTRAINT PK_ACTOR PRIMARY KEY,
ACTOR_NAME VARCHAR2(100) NOT NULL,
BIRTH_YEAR NUMBER(4),
GENDER VARCHAR2(10)
);
INSERT INTO ACTOR VALUES (1001, '김민준', 1980, '남');
INSERT INTO ACTOR VALUES (1002, '박서준', 1988, '남');
INSERT INTO ACTOR VALUES (1003, '이도현', 1995, '남');
INSERT INTO ACTOR VALUES (1004, '정우성', 1973, '남');
INSERT INTO ACTOR VALUES (1005, '황정민', 1970, '남');
INSERT INTO ACTOR VALUES (1006, '송강호', 1967, '남');
INSERT INTO ACTOR VALUES (1007, '이병헌', 1970, '남');
INSERT INTO ACTOR VALUES (1008, '하정우', 1978, '남');
INSERT INTO ACTOR VALUES (1009, '마동석', 1971, '남');
INSERT INTO ACTOR VALUES (1010, '조정석', 1980, '남');
INSERT INTO ACTOR VALUES (1011, '강하늘', 1990, '남');
INSERT INTO ACTOR VALUES (1012, '유연석', 1984, '남');
INSERT INTO ACTOR VALUES (1013, '변요한', 1986, '남');
INSERT INTO ACTOR VALUES (1014, '임시완', 1988, '남');
INSERT INTO ACTOR VALUES (1015, '류준열', 1986, '남');
INSERT INTO ACTOR VALUES (1016, '김태리', 1990, '여');
INSERT INTO ACTOR VALUES (1017, '전지현', 1981, '여');
INSERT INTO ACTOR VALUES (1018, '손예진', 1982, '여');
INSERT INTO ACTOR VALUES (1019, '김혜수', 1970, '여');
INSERT INTO ACTOR VALUES (1020, '한지민', 1982, '여');
INSERT INTO ACTOR VALUES (1021, '박보영', 1990, '여');
INSERT INTO ACTOR VALUES (1022, '수지', 1994, '여');
INSERT INTO ACTOR VALUES (1023, '김고은', 1991, '여');
INSERT INTO ACTOR VALUES (1024, '한소희', 1994, '여');
INSERT INTO ACTOR VALUES (1025, '천우희', 1987, '여');
INSERT INTO ACTOR VALUES (1026, '배두나', 1979, '여');
INSERT INTO ACTOR VALUES (1027, '이솜', 1990, '여');
INSERT INTO ACTOR VALUES (1028, '임윤아', 1990, '여');
INSERT INTO ACTOR VALUES (1029, '김다미', 1995, '여');
INSERT INTO ACTOR VALUES (1030, '신혜선', 1989, '여');
INSERT INTO ACTOR VALUES (1031, '이준호', 1990, '남');
INSERT INTO ACTOR VALUES (1032, '남주혁', 1994, '남');
INSERT INTO ACTOR VALUES (1033, '안효섭', 1995, '남');
INSERT INTO ACTOR VALUES (1034, '차은우', 1997, '남');
INSERT INTO ACTOR VALUES (1035, '도경수', 1993, '남');
INSERT INTO ACTOR VALUES (1036, '이제훈', 1984, '남');
INSERT INTO ACTOR VALUES (1037, '구교환', 1982, '남');
INSERT INTO ACTOR VALUES (1038, '유아인', 1986, '남');
INSERT INTO ACTOR VALUES (1039, '정해인', 1988, '남');
INSERT INTO ACTOR VALUES (1040, '김선호', 1986, '남');
INSERT INTO ACTOR VALUES (1041, '고아성', 1992, '여');
INSERT INTO ACTOR VALUES (1042, '라미란', 1975, '여');
INSERT INTO ACTOR VALUES (1043, '이하늬', 1983, '여');
INSERT INTO ACTOR VALUES (1044, '문채원', 1986, '여');
INSERT INTO ACTOR VALUES (1045, '정유미', 1984, '여');
INSERT INTO ACTOR VALUES (1046, '이정은', 1970, '여');
INSERT INTO ACTOR VALUES (1047, '염정아', 1972, '여');
INSERT INTO ACTOR VALUES (1048, '전여빈', 1989, '여');
INSERT INTO ACTOR VALUES (1049, '박은빈', 1992, '여');
INSERT INTO ACTOR VALUES (1050, '김세정', 1996, '여');
COMMIT;
CREATE TABLE MOVIE2 (
MOVIE2_ID NUMBER(4) CONSTRAINT PK_MOVIE2 PRIMARY KEY,
TITLE VARCHAR2(200) NOT NULL,
RELEASE_YEAR NUMBER(4),
GENRE_ID NUMBER(3) NOT NULL,
LEAD_ACTOR_ID NUMBER(4) NOT NULL,
SUPPORT_ACTOR_ID NUMBER(4) NOT NULL,
RUNNING_TIME NUMBER(3),
RATING NUMBER(2,1),
CONSTRAINT FK_MOVIE2_GENRE FOREIGN KEY (GENRE_ID)
REFERENCES GENRE (GENRE_ID),
CONSTRAINT FK_MOVIE2_LEAD_ACTOR FOREIGN KEY (LEAD_ACTOR_ID)
REFERENCES ACTOR (ACTOR_ID),
CONSTRAINT FK_MOVIE2_SUPPORT_ACTOR FOREIGN KEY (SUPPORT_ACTOR_ID)
REFERENCES ACTOR (ACTOR_ID),
CONSTRAINT CK_MOVIE2_ACTOR_DIFF CHECK (LEAD_ACTOR_ID <> SUPPORT_ACTOR_ID)
);
INSERT INTO MOVIE2 VALUES (2001, '서울의 그림자', 2021, 4, 1004, 1016, 118, 8.1);
INSERT INTO MOVIE2 VALUES (2002, '한강의 추적자', 2022, 5, 1005, 1019, 124, 8.3);
INSERT INTO MOVIE2 VALUES (2003, '마지막 수사', 2020, 10, 1036, 1025, 115, 7.9);
INSERT INTO MOVIE2 VALUES (2004, '푸른 별', 2023, 7, 1032, 1026, 130, 8.5);
INSERT INTO MOVIE2 VALUES (2005, '사라진 열쇠', 2021, 10, 1013, 1030, 109, 7.8);
INSERT INTO MOVIE2 VALUES (2006, '도심의 밤', 2022, 4, 1007, 1024, 117, 8.0);
INSERT INTO MOVIE2 VALUES (2007, '작전명 블랙', 2024, 1, 1009, 1043, 128, 8.6);
INSERT INTO MOVIE2 VALUES (2008, '우리들의 계절', 2020, 2, 1010, 1018, 112, 7.7);
INSERT INTO MOVIE2 VALUES (2009, '웃음 바이러스', 2021, 3, 1011, 1021, 105, 7.5);
INSERT INTO MOVIE2 VALUES (2010, '달빛 로맨스', 2023, 6, 1039, 1022, 114, 8.2);
INSERT INTO MOVIE2 VALUES (2011, '비상구 없는 건물', 2024, 4, 1008, 1048, 121, 8.4);
INSERT INTO MOVIE2 VALUES (2012, '철의 도시', 2022, 1, 1009, 1017, 126, 8.1);
INSERT INTO MOVIE2 VALUES (2013, '기억의 조각', 2021, 2, 1038, 1045, 119, 7.9);
INSERT INTO MOVIE2 VALUES (2014, '붉은 신호', 2020, 5, 1006, 1042, 123, 8.0);
INSERT INTO MOVIE2 VALUES (2015, '은하 정거장', 2025, 7, 1033, 1029, 132, 8.7);
INSERT INTO MOVIE2 VALUES (2016, '유리정원 사건', 2023, 10, 1037, 1044, 111, 8.1);
INSERT INTO MOVIE2 VALUES (2017, '바람의 편지', 2022, 6, 1012, 1023, 108, 7.6);
INSERT INTO MOVIE2 VALUES (2018, '골목의 영웅', 2021, 1, 1031, 1049, 116, 7.8);
INSERT INTO MOVIE2 VALUES (2019, '그날의 증인', 2020, 4, 1014, 1041, 113, 7.7);
INSERT INTO MOVIE2 VALUES (2020, '도둑들의 휴가', 2024, 3, 1015, 1028, 107, 7.9);
INSERT INTO MOVIE2 VALUES (2021, '폭풍전야', 2023, 4, 1002, 1024, 120, 8.0);
INSERT INTO MOVIE2 VALUES (2022, '침묵의 강', 2021, 2, 1001, 1047, 117, 7.8);
INSERT INTO MOVIE2 VALUES (2023, '네온 시티', 2025, 7, 1034, 1048, 129, 8.3);
INSERT INTO MOVIE2 VALUES (2024, '검은 모래', 2022, 5, 1005, 1046, 122, 8.2);
INSERT INTO MOVIE2 VALUES (2025, '봄의 시작', 2020, 6, 1035, 1021, 104, 7.4);
INSERT INTO MOVIE2 VALUES (2026, '하늘 아래 우리', 2024, 2, 1039, 1050, 118, 8.1);
INSERT INTO MOVIE2 VALUES (2027, '미로 속으로', 2023, 10, 1036, 1027, 110, 7.9);
INSERT INTO MOVIE2 VALUES (2028, '스파이 코드', 2021, 1, 1007, 1043, 125, 8.2);
INSERT INTO MOVIE2 VALUES (2029, '7월의 눈', 2022, 2, 1011, 1030, 115, 7.7);
INSERT INTO MOVIE2 VALUES (2030, '해적선의 비밀', 2020, 8, 1008, 1017, 127, 7.8);
INSERT INTO MOVIE2 VALUES (2031, '작은 거인', 2021, 3, 1010, 1042, 106, 7.6);
INSERT INTO MOVIE2 VALUES (2032, '낯선 행성', 2025, 7, 1032, 1026, 134, 8.8);
INSERT INTO MOVIE2 VALUES (2033, '리허설 없는 삶', 2022, 2, 1038, 1045, 116, 8.0);
INSERT INTO MOVIE2 VALUES (2034, '심야 택시', 2023, 4, 1037, 1041, 112, 7.8);
INSERT INTO MOVIE2 VALUES (2035, '화이트 아웃', 2024, 1, 1009, 1029, 124, 8.4);
INSERT INTO MOVIE2 VALUES (2036, '무너진 진실', 2020, 5, 1006, 1019, 121, 8.1);
INSERT INTO MOVIE2 VALUES (2037, '너의 계절', 2021, 6, 1012, 1022, 109, 7.5);
INSERT INTO MOVIE2 VALUES (2038, '마지막 오디션', 2022, 3, 1031, 1028, 103, 7.4);
INSERT INTO MOVIE2 VALUES (2039, '봉인된 방', 2024, 10, 1013, 1044, 114, 8.2);
INSERT INTO MOVIE2 VALUES (2040, '별빛 아래', 2023, 8, 1033, 1016, 126, 8.0);
INSERT INTO MOVIE2 VALUES (2041, '검은 파도', 2025, 4, 1004, 1047, 119, 8.3);
INSERT INTO MOVIE2 VALUES (2042, '중앙역 11번 출구', 2021, 5, 1014, 1046, 118, 7.9);
INSERT INTO MOVIE2 VALUES (2043, '로봇의 하루', 2022, 7, 1035, 1049, 131, 8.1);
INSERT INTO MOVIE2 VALUES (2044, '겨울 편지', 2020, 2, 1001, 1018, 111, 7.6);
INSERT INTO MOVIE2 VALUES (2045, '캠퍼스 소동', 2023, 3, 1040, 1050, 102, 7.3);
INSERT INTO MOVIE2 VALUES (2046, '붉은 복도', 2024, 4, 1002, 1025, 117, 8.0);
INSERT INTO MOVIE2 VALUES (2047, '추격자들', 2022, 1, 1007, 1043, 123, 8.2);
INSERT INTO MOVIE2 VALUES (2048, '시간 수집가', 2025, 8, 1034, 1023, 128, 8.5);
INSERT INTO MOVIE2 VALUES (2049, '종이비행기', 2021, 6, 1039, 1021, 107, 7.7);
INSERT INTO MOVIE2 VALUES (2050, '유리문 너머', 2023, 10, 1036, 1048, 113, 8.1);
COMMIT;
각 영화에 대해 영화의 제목, 장르, 주연배우의 이름, 조연배우의 이름, 평점을 조회한다.
select m.title as 영화제목,
g.genre_name as 장르명,
a1.actor_name as 주연배우이름,
a2.actor_name as 조연배우이름 ,
m.rating as 평점
from movie2 m, actor a1, actor a2, genre g
where m.lead_actor_id = a1.actor_id
and m.support_actor_id = a2.actor_id
and g.genre_id = g.GENRE_ID;
movie2 테이블의 주연배우(lead_actor_id), 조연배우(support_actor_id)는 각각 한 번씩 actor 테이블의 actor_id와 조인하였다. movie2테이블의 주연배우, 조연배우를 각각 한 번씩 조회하기 때문에 actor 테이블은 a1, a2 라는 이름으로 각 열에 따로 한 번씩 조인된다.
actor ---(lead_cator_id)---> movie2 <---(support_actor_id)--- actor
또한 movie2테이블의 장르(genre_id)는 genre 테이블의 (genre_id)와 조인된다.
actor ---(lead_cator_id)---> movie2 <---(support_actor_id)--- actor
↑
(genre_id)
|
genre
-02 OUTER JOIN
INNER JOIN의 반대이다.
조인 조건을 만족하지 않아도 데이터 생략을 하지 않는 출력을 원할 때 사용한다.
left, right, full outer join 등이 있으며 기준이 되는 테이블이 어느 방향에 위치하냐에 따라 달라진다.
오라클의 경우 from 절 내 테이블의 순서는 중요하지 않으나 기준이 되는 테이블 반대 조인 조건에 (+)를 붙여야 한다.
여러 테이블이 조인되는 상황에서 아래와 같은 관계가 정의된 경우 사용하는 조인이다.
S - P (+) - D (+)
S 테이블 기준으로 P 테이블에 대한 아우터 조인 실행 시 D 테이블까지 outer join이 수행되어야 한다.
outer join 시 여러 칼럼으로 조인 조건을 사용하는 경우 기준이 되는 컬럼 반대쪽의 테이블(컬럼)에 (+) 기호를 붙여야 한다.
where to_char(hiredate, 'yyyy') between to_char(b.hiredate(+), 'yyyy')
and to_char(b.hiredate(+), 'yyyy')
student, department, professor 총 3개의 테이블을 사용하여 각 학생의 이름, 지도교수의 이름, 지도교수의 소속학과명을 출력한다. 단, 지도교수가 없는 학생도 출력한다.
select s.name as 학생이름,
p.name as 지도교수이름,
d.dname as 지도교수소속학과명
from student s, professor p, department d
where s.profno(+) = p.profno(+)
and p.deptno = d.deptno(+);
에러가 발생하였다. 기준이 되는 student의 profno 컬럼에 (+) 를 붙임으로써 양 쪽 테이블이 기준이 되어버렸기 때문이다. 이는 full outer join이라 하며 오라클은 이를 금지하고 있어 에러가 발생하게 된다.
select s.name as 학생이름,
p.name as 지도교수이름,
d.dname as 지도교수소속학과명
from student s, professor p, department d
where s.profno = p.profno(+)
and p.deptno = d.deptno(+);
지도교수가 배정되지 않은 학생들의 정보까지 조회되었음을 확인할 수 있다.
-03 SELF JOIN
하나의 테이블 내에서 서로 다른 행이 관계를 맺고 있을 시 사용하는 조인이다.
관계를 맺는 행끼리 비교하며, 동시에 한 행으로 출력을 한다.
또한 관계를 통해 생략되는 행이 존재할 수 있는 경우가 많기에 self join 시 outer join 사용을 반드시 고려해야 한다.
department 테이블을 사용해 각 학과의 이름, 상위학과 이름을 출력한다.
단, 상위학과가 없는 학과도 포함하여 출력한다.
DNAME 열을 확인해 보았을 때, 학과와 상위학과가 같은 열에 존재함을 확인할 수 있다.
DEPTNO 열을 확인해 보았을 때, 학과와 학부의 코드 체계가 다름을 확인할 수 있다. 또한 PART 열의 데이터도 잘 살펴보는 것이 좋다.
select d1.dname as 학과이름,
d2.dname as 상위학과이름
from department d1, department d2
where d1.part = d2.deptno(+);
따라서 part열과 deptno 열을 조인하여 다음과 같은 결과를 출력하였다.
member, member_club, club 총 3개의 테이블을 사용하여 각 고객의 이름, 추천인명, 가입한 클럽의 이름, 클럽 가입 날짜를 출력하겠다.
member 테이블은 고객번호, 이름, 추천인명, 등급, 가입일로 구성되어 있다.
member_club 테이블은 고객번호, 클럽번호, 활동유무, 가입일로 구성되어 있다.
club 테이블은 클럽번호, 클럽명, 클럽의 종류, 개설일로 구성되어 있다.
select m1.MEMBER_NAME as 멤버이름,
m2.MEMBER_name as 추천인이름,
c.club_name as 클럼명,
mc.join_date as 가입일
from member m1, member m2, member_club mc, club c
where m1.RECOMMENDER_ID = m2.member_id(+)
and m1.member_id = mc.member_id(+)
and mc.club_id = c.club_id(+);
select *
from member;
member (+) - member - member_clup (+) - club(+)
클럽활동을 하지 않는 사람도 있고, 추천인이 없는 사람도 있기 때문에 위와 같은 구조를 잘 생각하며 조인 조건을 지정해야 한다.
02 JOIN의 두 가지 문법 (표준 조인)
ANSI 표준 조인은 오라클과 문법이 다르다.
from 절에서 테이블 및 테이블별칭을 지정하고 where 절에서 조인 조건을 지정하는 오라클 문법과 다르게, from에서 테이블명 및 사용하고자하는 조인의 종류를 지정하고 on 절에서 조건을 지정한다.
1) from 절의 테이블과 테이블 사이에 조인의 명칭을 전달한다.
from tab1 ... tab2
2) 조인 조건은 on 절, 일반조건은 where 절에 각각 구분하여 전달한다.
from ~ on~ 이 한 세트이며, where는 on 아래에 작성한다.
from tab1 ... tab2
on (tab1.no = tab2.no) -- 괄호 생략 가능
where tab1.name = "....";
-01 INNER JOIN
조인 조건을 만족하는 행만 출력하는 조인의 형태이며, 'inner'는 생략 가능하다.
ex) from tab1 [inner] join tab2
student, professor 테이블 2개를 사용하여 4학년 학생의 이름, 학년, 지도교수이름을 출력할 시 문법은 다음과 같다.
- 오라클 표준)
select s.name, s.grade, p.name
from student s, professor p
where s.profno = p.profno
and s.grade = 4;
- ANSI 표준)
select s.name, s.grade, p.name
from student s inner join professor p
on s.profno = p.profno
where s.grade = 4;
-02 OUTER JOIN
조인 조건을 만족하지 않아도 데이터 생략 없이 출력을 원할 떄 사용하는 조인이다.
기준이 되는 테이블 방향에 따라 left / right / full outer join로 구성된다.
--outer 는 생략이 가능하다.
ex) from tab1 left [outer] join tab2
on tab1.no = tab2.no;
student, professor 테이블 2개를 사용하여 1학년 학생에 대해 학생이름, 지도교수이름 출력
- oracle 표준)
1학년 학생들은 지도교수가 없기 때문에 결과가 출력되지 않는다
select s.name, p.name
from student s, professor p
where s.profno = p.profno
and s.grade = 1;
따라서 (+)를 붙여서 outer join
select s.name, p.name
from student s, professor p
where s.profno = p.profno(+)
and s.grade = 1;
- ansi 표준)
select s.name, p.name
from student s left outer join professor p
on s.profno = p.profno
where s.grade = 1;
-03 NATURAL JOIN
조인조건 없이 양쪽 테이블에 동일한 이름의 컬럼이 있으며, 컬럼의 값이 같은 경우 연결(equi join)되는 조인이다.
natural join에 대한 오라클 표준은 존재하지 않는다.
테이블만 사용하면 되기에 on절은 사용하지 않는다.
student 테이블과 exam_01 테이블을 natural join 시, s.studno = e.studno가 자동으로 실행된다.
항상 값이 같음을 가정하고 있기에 해당 열의 출처를 명시할 필요가 없으며, 식별자를 가질 수 없다. (컬럼구분자 지정 불가능)
select s.name, e.total
from student s natural join exam_01 e;
select * from professor;
select * from student;
select *
from student s natural join professor p;
그러나 위의 예제와 같이 2개 이상의 열 이름이 같을 경우 공집합이 출력된다.
두 테이블은 profno 뿐만이 아니라 name 열의 이름도 같은데, 동일한 컬럼값의 값을 출력하기 때문에
s.name = p.name and s.profno and p.profno 가 자동으로 실행된다.
교수의 이름 정보와 학생의 이름 정보가 모두 같을 수는 없기 때문에 공집합이 출력되었음을 확인할 수 있다.
-04 CROSS JOIN
모든 발생 가능한 조합을 출력하는 조인이며, 조인 조건 전달이 불가능하다.
오라클은 별도의 표준 조건을 지정하지 않고 조인을 진행할 시 cross join에 해당된다.
-- ansi 표준)
select *
from emp cross join dept;
-05 FULL OUTER JOIN
양쪽 테이블 기준으로 조인 조건에 맞지 않는 데이터를 모두 출력하는 조인 기법이다.
(left outer join 결과) union (right outer join 결과)과 같다.
-06 USING 절
ANSI 표준에 존재하는 조인을 위한 절이며, 양쪽 테이블의 동일한 컬럼명의 값이 일치하는 경우 사용한다.
using절에 사용된 컬럼은 컬럼구분자 전달이 불가능하다.
다음과 같은 경우 괄호는 생략 가능하다.
select s.studno, s.name, e.total
from student s join exam_01 e
on (s.studno = e.studno);
그러나 다음과 같은 경우 괄호는 생략이 불가능하다.
select studno, s.name, e.total
from student s join exam_01 e
using (studno);
결과는 모두 동일하다.
'아이티윌_데이터 분석 55기 > 강의내용 필기_SQL' 카테고리의 다른 글
| #9 9일차_서브쿼리 : 인라인뷰, 스칼라 서브쿼리 (0) | 2026.03.17 |
|---|---|
| #8 8일차_서브쿼리 (0) | 2026.03.16 |
| #6 6일차_ JOIN : equi join, non equi join, 세 개 이상 테이블의 조인 (0) | 2026.03.12 |
| #5 5일차_날짜 파싱 시 유의사항, 변환 함수, NULL 치환 함수 (0) | 2026.03.11 |
| #4 4일차_조건 치환, 숫자 함수, 날짜 함수 (0) | 2026.03.10 |