SQL - 고급[조인] - 조인(join), INNER JOIN(내부 조인), OUTER JOIN(외부조인), CROSS JOIN(상호 조인), SELF JOIN(자체 조인), UNION / UNION ALL / NOT IN / IN

Chap.7-3 SQL 고급[조인] - 조인(join), INNER JOIN(내부 조인), OUTER JOIN(외부조인), CROSS JOIN(상호 조인), SELF JOIN(자체 조인), UNION / UNION ALL / NOT IN / IN

조인

조인(join), INNER JOIN(내부 조인), OUTER JOIN(외부조인), CROSS JOIN(상호 조인), SELF JOIN(자체 조인), UNION / UNION ALL / NOT IN / IN

조인(join)

두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것
관계형 테이블의 가장 큰 특징
테이블 간의 관계

▪ 1:1 관계

▪ 1:N 관계 - ☆

▪ N:M 관계
가장 많이 사용되는 조인
공통 컬럼을 기반으로 결합

▪ 부모 테이블 : Primary Key

▪ 자식 테이블 : Foreign Key(부모 테이블의 PK 값 참조)

[형식]

SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색 조건]

INNER JOIN(내부 조인)

USE sqlDB;

SELECT *
FROM buyTBL
	INNER JOIN userTBL
	ON buyTBL.userID = userTBL.userID
WHERE buyTBL.userID = 'BBK'; -- 이름이 중복되면 테이블 명과 같이 줘야한다.

결과

메커니즘

전부 출력

USE sqlDB;

SELECT *
FROM buyTBL
	INNER JOIN userTBL -- 있는 것만 출력된다.
	ON buyTBL.userID = userTBL.userID;

결과

SELECT userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처' 
FROM buytbl
	INNER JOIN usertbl
	ON buyTBL.userID = userTBL.userID ;

결과

오류 (userID가 중복된다.)

필요한 것만 출력

SELECT buyTBL.userID, name, prodName, addr, CONCAT(mobile1,mobile2) AS '연락처'
-- 어디서 충돌할지 몰라 다 나열한 것
FROM buyTBL
	INNER JOIN userTBL
	ON buyTBL.userID = userTBL.userID ;

결과

Table을 포함 시켜야 중복되지 않는다.

Table 모두 지정

SELECT buyTBL.userID, userTBL.name, buyTBL.prodName, userTBL.addr,
	CONCAT(userTBL.mobile1, userTBL.mobile2) AS '연락처'
FROM buyTBL
	INNER JOIN userTBL
	ON buyTBL.userID = userTBL.userID;

결과

Table 간략하게

SELECT B.userID, U.name, B.prodName, U.addr,
	CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buyTBL B
	INNER JOIN userTBL U
	ON B.userID = U.userID;

WHERE절 이용

SELECT B.userID, U.name, B.prodName, U.addr,
	CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buyTBL B
	INNER JOIN userTBL U
	ON B.userID = U.userID
WHERE B.userID = 'JYP';

결과

ORDER BY 이용

SELECT B.userID, U.name, B.prodName, U.addr,
	CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buyTBL B
	INNER JOIN userTBL U
	ON B.userID = U.userID
WHERE B.userID = 'JYP'
ORDER BY U.userID;

결과

구매 이력이 있는 회원만 출력됨

JOIN 이용 확인

SELECT DISTINCT U.userID, U.name, U.addr
FROM userTBL U
	INNER JOIN buyTBL B
	ON U.userID = B.userID
ORDER BY U.userID ;

결과

SubQuery 이용 확인

SELECT U.userID, U.name, U.addr
FROM userTBL U
WHERE EXISTS (
	SELECT *
	FROM buyTBL B
	WHERE U.userID = B.userID );

결과

예제

지역별 배송 수량, 매출액 출력

SELECT U.addr, SUM(B.amount) '수량', SUM(B.amount*B.price) '매출액'
FROM usertbl U
 INNER JOIN buytbl B
 ON B.userID = U.userID
GROUP BY U.addr;

결과

3개의 테이블 조인

테스트 데이터

USE sqlDB;
CREATE TABLE stdTBL(
 stdName VARCHAR(10) NOT NULL PRIMARY KEY, -- NULL을 허용하지 않음
 addr CHAR(4) NOT NULL
);

CREATE TABLE clubTBL(
 clubName VARCHAR(10) NOT NULL PRIMARY KEY,
 roomNo CHAR(4) NOT NULL
);

CREATE TABLE stdclubTBL(
 num int AUTO_INCREMENT NOT NULL PRIMARY KEY,
 stdName VARCHAR(10) NOT NULL,
 clubName VARCHAR(10) NOT NULL,						-- 컬럼이 다 정의되어야 한다.
 FOREIGN KEY(stdName) REFERENCES stdTBL(stdName),   -- ☆☆☆
 FOREIGN KEY(clubName) REFERENCES clubTBL(clubName) -- ☆☆☆
);

INSERT INTO stdTBL VALUES
(N'김범수', N'경남'), (N'성시경', N'서울'), (N'조용필', N'경기'),
(N'은지원', N'경북'), (N'바비킴', N'서울');

INSERT INTO clubTBL VALUES
(N'수영', N'101호'), (N'바둑', N'102호'), (N'축구', N'103호'),
(N'봉사', N'104호');

INSERT INTO stdclubTBL VALUES
(NULL, N'김범수', N'바둑'), (NULL, N'김범수', N'축구'),
(NULL, N'조용필', N'축구'), (NULL, N'은지원', N'축구'),
(NULL, N'은지원', N'봉사'), (NULL, N'바비킴', N'봉사');

3개의 테이블 조인

SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdTBL S
  INNER JOIN stdclubTBL SC
	ON S.stdName = SC.stdName
  INNER JOIN clubTBL C
	ON SC.clubName = C.clubName
ORDER BY S.stdName;

결과

OUTER JOIN(외부조인)

내부 조인은 값이 있는 행 만을 대상으로 함
OUTER JOIN은 모든 행에 대해 조인을 함

[형식]

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
  <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
    ON <조인될 조건>
[WHERE 검색 조건];

- LEFT JOIN : 왼쪽 테이블의 모든 행을 기준으로 조인

- RIGHT JOIN : 오른쪽 테이블의 모든 행을 기준으로 조인

- FULL : 두 테이블의 모든 행에 대해 조인

Outer Join은 조인의 조건에 만족되지 않는 행까지도 포함시킨다.

LEFT OUTER JOIN

USE sqlDB;
SELECT U.userID, U.name, B.prodName, U.addr,
	CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM userTBL U
  LEFT OUTER JOIN buyTBL B
    ON U.userID = B.userID
ORDER BY U.userID;

결과

RIGHT OUTER JOIN

SELECT U.userID, U.name, B.prodName, U.addr,
	CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buyTBL B
  RIGHT OUTER JOIN userTBL U
    ON U.userID = B.userID
ORDER BY U.userID;

결과

구매이력이 없는 사용자만 출력

SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM userTBL U
  LEFT OUTER JOIN buyTBL B
    ON U.userID = B.userID
WHERE B.prodName IS NULL  -- B.prodName = NULL 이렇게 하면 안된다!
ORDER BY U.userID;
-- IS NOT 이었으면 구매이력이 있는 사람들

결과

예제

모든 사용자에 대한 구매액

SELECT U.userID, U.name, SUM(B.price*B.amount) AS '총구매액'
FROM userTBL U
  LEFT OUTER JOIN buyTBL B
    ON U.userID = B.userID
GROUP BY U.userID
ORDER BY U.userID;

결과

NULL 값에 대한 산술연산 : NULL
NULL 값에 대한 비교연산 : Unknown

NULL 부분 보기 좋게 처리

SELECT U.userID, U.name, IFNULL(SUM(B.price*B.amount), 0) AS '총구매액'
FROM userTBL U
  LEFT OUTER JOIN buyTBL B
    ON U.userID = B.userID
GROUP BY U.userID
ORDER BY U.userID;

결과

CROSS JOIN(상호 조인)

두 테이블의 가능한 모든 조합을 구성 (M X N)

[형식]

USE sqlDB;
SELECT *
	FROM buyTBL
    	CROSS JOIN userTBL;

SELF JOIN(자체 조인)

한 개의 테이블에 대해 자체 조인을 수행하는 것
한 개의 테이블을 두 개의 테이블로 간주 - ☆ 별칭으로 관리

▪ Alias를 두개 배정하여 구분
대표적인 예

▪ 조직도

USE sqlDB;

CREATE TABLE empTbl (emp CHAR(3), manager CHAR(3), empTel VARCHAR(8));

INSERT INTO empTbl VALUES
(N'나사장',NULL,'0000'),
(N'김재무',N'나사장','2222'),
(N'김부장',N'김재무','2222-1'),
(N'이부장',N'김재무','2222-2'),
(N'우대리',N'이부장','2222-2-1'),
(N'지사원',N'이부장','2222-2-2'),
(N'이영업',N'나사장','1111'),
(N'한과장',N'이영업','1111-1'),
(N'최정보',N'나사장','3333'),
(N'윤차장',N'최정보','3333-1'),
(N'이주임',N'윤차장','3333-1-1');

우대리 상관의 연락처 확인

SELECT A.emp AS '부하직원' , B.emp AS '직속상관', B.empTel AS '직속상관연락처'
  FROM empTbl A
    INNER JOIN empTbl B
      ON A.manager = B.emp
  WHERE A.emp = '우대리';

결과

UNION / UNION ALL / NOT IN / IN

[형식]

SELECT 문장1
	UNION [ALL]
SELECT 문장2

UNION : 중복 제거, UNION ALL : 중복 모두 포함

USE sqlDB;
SELECT stdName, addr FROM stdTBL
UNION ALL
SELECT clubName, roomNo FROM clubTBL;

결과

차 집합

SELECT name, CONCAT(mobile1, mobile2) AS '전화번호' FROM userTBL
WHERE name NOT IN ( SELECT name FROM userTBL WHERE mobile1 IS NULL) ;

결과

교 집합

SELECT name, CONCAT(mobile1, mobile2) AS '전화번호' FROM userTBL
WHERE name IN ( SELECT name FROM userTBL WHERE mobile1 IS NULL) ;

결과

챕터, 데이터베이스당 하나의 쿼리를 써보자
Ctrl + Space : 자동완성

예제

employees 데이터 베이스
- employees TABLE에는 부서명이 없다.

- departments TABLE에 dept_no과 dept_name이 있다.

- dept_emp TABLE에 dept_no와 부서에 있었던 기간 정보가 있다.

- dept_emp TABLE에 dept_no와 departments TABLE의 dept_no가 연결된다.

employees를 출력하는데 부서명, 부서에 있었던 기간을 출력하라.

USE employees;

SELECT E.first_name, E.last_name, D.dept_name, DE.from_date, DE.to_date
FROM employees E
	INNER JOIN dept_emp DE
	ON E.emp_no = DE.emp_no
	INNER JOIN departments D
	ON DE.dept_no = D.dept_no
ORDER BY E.first_name, E.last_name; -- 이걸 해주어야 사람별로 결과가 나온다.

결과

현재 부서명을 출력하라.

SELECT E.first_name, E.last_name, D.dept_name, DE.from_date, DE.to_date
FROM employees E
	INNER JOIN dept_emp DE
	ON E.emp_no = DE.emp_no
	INNER JOIN departments D
	ON DE.dept_no = D.dept_no
WHERE DE.to_date = '9999-01-01'
ORDER BY E.first_name, E.last_name;

결과

저작자표시

'인터페이스 개발 > SQL - MariaDB' 카테고리의 다른 글

SQL - 테이블과 뷰[뷰] - 뷰(View), 뷰 만들기, 뷰 삭제 (0)	2020.07.29
SQL - 테이블과 뷰[테이블] - 테이블 만들기, 제약 조건, 테이블 삭제, 테이블 수정 (0)	2020.07.29
SQL - 고급[MariaDB 내장 함수] - 제어 흐름 함수, 문자열 함수, 수학 함수, 날짜 및 시간 함수, 시스템 정보 함수, 파일을 이용한 데이터 조작 (0)	2020.07.28
SQL - 고급[MariaDB의 데이터 형식] - 숫자 , 문자 , 날짜, 시간, 기타, LONGTEXT, LONGBLOB, 데이터 형식과 형 변환, 암시적 형변환 (0)	2020.07.28
SQL - 기본[데이터 변경을 위한 SQL 문] - INSERT, AUTO_INCREMENT, 다중 입력, 대량의 샘플 데이터 생성, UPDATE, DELETE FROM (0)	2020.07.28

cooluk

SQL - 고급[조인] - 조인(join), INNER JOIN(내부 조인), OUTER JOIN(외부조인), CROSS JOIN(상호 조인), SELF JOIN(자체 조인), UNION / UNION ALL / NOT IN / IN

'인터페이스 개발 > SQL - MariaDB' 카테고리의 다른 글

댓글

티스토리툴바

SQL - 고급[조인] - 조인(join), INNER JOIN(내부 조인), OUTER JOIN(외부조인), CROSS JOIN(상호 조인), SELF JOIN(자체 조인), UNION / UNION ALL / NOT IN / IN

'인터페이스 개발 > SQL - MariaDB' 카테고리의 다른 글

관련글

댓글

티스토리툴바