JOIN 문 / 서브 쿼리

조인 (Join)

A = {1,2,3,4}
B = {3,4,5,6,7}
-> 결과 = { ? }

두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것.
(사용자가 필요한 집합 결과 도출)
가장 많이 사용하는 조인은 inner join > outer join > self join > cross join > natural join

INNER JOIN

A와 B의 교집합
조인하는 테이블의 ON 절의 조건이 일치하는 결과만 출력
inner join은 EQUI join이라고도 하고, 그냥 join이라고도 하며, 등가 조인이라고도 표현한다.

LEFT JOIN

= LEFT OUTER JOIN
LEFT JOIN은 두 테이블이 있을 경우, 첫 번째 테이블을 기준으로 두 번째 테이블을 조합하는 JOIN

RIGHT JOIN

= RIGHT OUTER JOIN
RIGHT JOIN은 두 테이블이 있을 경우, 두 번째 테이블을 기준으로 첫 번째 테이블을 조합하는 JOIN

FULL OUTER JOIN

이름에서도 알 수 있는 FULL의 의미는 A와 B 전체를 구하는 것임.
SELECT * FROM TableA A FULL OUTER JOIN TableB B ON A.NAME = B.NAME;
MySQL은 FULL OUTER JOIN을 지원X

CROSS JOIN

두 집합의 곱집합 출력
한 테이블의 모든 행과 다른 테이블의 모든 행이 연결되는 모든 경우를 반환

SELF JOIN

동일 집합끼리 특정 컬럼 기준으로 집합 출력
말 그대로 테이블 자기자신을 조인한 것

UNION과 UNION ALL

UNION은 여러 개의 SELECT 문의 결과를 하나의 테이블이나 결과 집합으로 표현할 때 사용
이때 각각의 SELECT 문으로 선택된 필드의 개수와 타입은 모두 같아야 하며, 필드의 순서 또한 같아야 함.
UNION은 여러 테이블의 쿼리의 결과를 합칠 때 중복된 행은 제거함.
UNION ALL은 여러 테이블의 쿼리의 결과를 합칠 때 중복된 행은 제거하지 않음.

Ex. Join

기본 구성

Table A

id	name
1	홍길동
2	서길동
3	남길동

Table B

id	name
A	서길동
B	남길동
C	북길동

INNER JOIN

inner join = 교집합

SELECT TableA.ID, TableA.NAME, TableB.ID, TableB.NAME
FROM TableA INNER JOIN TableB  // FROM TableA, TableB
ON TableA.NAME = TableB.NAME
where TableA.NAME = 서길동;  //join을 완료하고 그 다음 조건을 따진다.

또는

alias 사용

SELECT A.ID, A.NAME, B.ID, B.NAME
FROM TableA A INNER JOIN TableB B  //FROM TableA A, TableB B
ON A.NAME = B.NAME
where A.NAME = 서길동;  //join을 완료하고 그 다음 조건을 따진다.

결과)

‘2’,’서길동’,’A’,’서길동’
‘3’,’남길동’,’B’,’남길동’(X)

LEFT JOIN

= LEFT OURTER JOIN

SELECT tableA.ID, TableA.NAME, TableB.ID, TableB.NAME
FROM TableA LEFT JOIN TableB
ON TableA.NAME = TableB.NAME
WHERE TableB.NAME IS NULL;

또는

SELET A.ID, A.NAME, B.ID, B.NAME
FROM TableA A LEFT JOIN TableB B
ON A.NAME = B.NAME;

결과)

‘1’,’홍길동’,NULL,NULL
‘2’,’서길동’,’A’,’서길동’
‘3’,’남길동’,’B’,’남길동’

RIGHT JOIN

SELECT TableA.ID, TableA.NAME, TableB.ID, TableB.NAME
FROM TableA RIGHT JOIN TableB
ON TableA.NAME = TableB.NAME;

또는

SELECT TableA.ID, TableA.NAME, TableB.ID, TableB.NAME
FROM TableA RIGHT JOIN TableB
ON TableA.NAME = TableB.NAME;

결과)

‘2’,’서길동’,’A’,’서길동’
‘3’,’남길동’,’B’,’남길동’
NULL, NULL,’C’,’북길동’

CROSS JOIN

SELECT TableA.ID, TableA.NAME, TableB.ID, TableB.NAME
FROM TableA CROSS JOIN TableB
ORDER BY TableA.ID, TableB.ID;

또는

SELECT A.ID, A.NAME, B.ID, B.NAME
FROM TableA A CROSS JOIN TableB B
ORDER BY A.ID, B.ID;

결과)

‘1’,’홍길동’,’A’,’서길동’
‘1’,’홍길동’,’B’,’남길동’
‘1’,’홍길동’,’C’,’북길동’
‘2’,’서길동’,’A’,’서길동’
‘2’,’서길동’,’B’,’남길동’
‘2’,’서길동’,’C’,’북길동’
‘3’,’남길동’,’A’,’서길동’
‘3’,’남길동’,’B’,’남길동’
‘3’,’남길동’,’C’,’북길동’

SELF JOIN

SELECT A.ID, A.NAME, B.ID, B.NAME
FROM TableA A JOIN TableA B
ON A.NAME <> B.NAME order by A.ID;

결과)

‘1’,’홍길동’,’2’,’서길동’
‘1’,’홍길동’,’3’,’남길동’
‘2’,’서길동’,’1’,’홍길동’
‘2’,’서길동’,’3’,’남길동’
‘3’,’남길동’,’1’,’홍길동’
‘3’,’남길동’,’2’,’서길동’

UNION과 UNION ALL

SELECT * FROM TableA UNION SELECT * FROM TableB;
SELECT * FROM TableA UNIAL ALL SELECT * FROM TableB;

서브 쿼리(Subquery)

서브 쿼리(Subquery)란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미함.
서브 쿼리를 포함하고 있는 쿼리를 외부 쿼리(outer query)라고 부르며,
서브 쿼리는 내부 쿼리(inner query)라고 부름.

01. 서브 쿼리 실행 순서

서브쿼리 실행 -> 메인(부모) 쿼리 실행

select * from t1
where target_id in (select id from sub_t2 where id < 100);

서브 쿼리는 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말함.
(select id from sub_t2 where id < 100) 같이 괄호() 안에 있는 쿼릴르 서브 쿼리라 말함.
서브 쿼리(=자식 쿼리, 내부 쿼리) : 메인 쿼리 컬럼 사용 가능
메인 쿼리(=부모 쿼리, 외부 쿼리) : 서브 쿼리 컬럼 사용 불가

02. 서브 쿼리의 위치에 따른 명칭

SELECT col1, (SELECT …) - 스칼라 서브쿼리(Scalar Sub Query)
하나의 컬럼처럼 사용 (표현 용도)
FROM (SELECT …) - 인라인 뷰(Inline View)
하나의 테이블처럼 사용 (테이블 대체 용도)
WHERE col = (SELECT …) - 일반 서브 쿼리
하나의 변수(상수)처럼 사용 (서브 쿼리의 결과에 따라달라지는 조건절)

Ex.

일반 서브 쿼리
WHERE 문에 나타나는 서브쿼리

select name, height
from usertbl
where height > 177;
// 조건 값을 상수로 할 때

Untitled

select name, height
from usertbl
where height > (select height from usertbl where name in ('김경호'));
// 조건 값을 select로 특정할 때 (단 결과가 값이 하나여야 됨)

Untitled (1)

select name, height
from usertbl
where height = any(select height from uesrtbl where addr in ('경남'));

// 조건에 값이 여러 개 들어올 땐 any,
any는 in 과 동일한 의미,
or를 의미함.

Untitled (2)

select *
from usertbl
where height > all(select height from uesrtbl where addr in ('경남'));

//all은 도출된 모든 조건 값에 대해 만족할 때, and를 의미함.

Untitled (3)

인라인 뷰(Inline View)

FROM 문에 나타는 서브쿼리
참고로 서브 쿼리가 FROM 절에 사용되는 경우 무조건 AS 별칭을 주어야 한다.

SELECT ex1.userID,ex1.name
FROM (
    SELECT *
    FROM usertbl AS ex2
    WHERE ex2.addr='서울'
) ex1;  -- 서브쿼리 별칭

Untitled (4)

스칼라 서브쿼리 (Scalar Subquery)

SELECT 문에 나타나는 서브 쿼리
딴 테이블에서 어떠한 값을 가져올 때 쓰임
하나의 레코드만 리턴이 가능하며, 두 개 이상의 레코드는 리턴할 수 없다.

SELECT D.userID, (SELECT MIN(price) FROM buytbl WHERE userID = D.userID) as Price FROM usertbl D;

Ex. 서브쿼리

CREATE TABLE employee (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64),
  salary INT,
  office_worker VARCHAR(64)
)

INSERT INTO employee VALUES(1,'허사장',20000000,'사장');
INSERT INTO employee (name,salary,office_worker) VALUES('유부장',10000000,'부장');
INSERT INTO employee (name,salary,office_worker) VALUES('박차장',5000000,'차장');
INSERT INTO employee (name,salary,office_worker) VALUES('정과장',4000000,'과장');
INSERT INTO employee (name,salary,office_worker) VALUES('정대리',3895000,'대리');
INSERT INTO employee (name,salary,office_worker) VALUES('노사원',2500000,'사원');
INSERT INTO employee (name,salary,office_worker) VALUES('하사원',2000000,'사원');
INSERT INTO employee (name,salary,office_worker) VALUES('길인턴',1000000,'인턴');

정대리라는 사람의 직급을 구하시오.

SELECT office_worker
FROM employee
WHERE office_worker = (SELECT office_worker FROM employee WHERE name = '정대리')

정대리보다 급여가 높은 사람들을 구하시오.

SELECT *
FROM employee
WHERE salary > (
    SELECT salary
    FROM employee
    WHERE NAME = '정대리'
)

직급이 사원인 사람들을 구하시오.

SELECT *
FROM employee
WHERE office_worker IN (
    SELECT office_worker
    FROM employee
    WHERE office_worker = '사원'
)

직급이 사원인 사람들의 이름과 급여를 구하시오.

SELECT EX1.name,EX1.salary
FROM (
    SELECT  *
    FROM employee AS Ii
    WHERE Ii.office_worker='사원') EX1;

정대리 급여와 테이블 전체 평균 급여를 구하시오.

SELECT name, salary, (
    SELECT ROUND(AVG(salary),-1)
    FROM employee) AS '평균급여'
FROM employee
WHERE name = '정대리'
)