[MariaDB] 6. SubQuery 와 JOIN

JIWON·2025년 5월 8일
DatabaseMariaDB

MariaDB

목록 보기
7/13
post-thumbnail

SubQuery

1. SET 연산

SET 연산 : 2개 이상의 테이블로부터 데이터를 추출하는 방법 중 하나
SET 연산자를 이용해서 여러개의 SELECT 문장을 연결해서 사용이 가능하다

기본 형식

SELECT 구문

SET operator

SELECT 구문

[ORDER BY]

주의할점

  • 첫 번째 SELECT 구문과 두번째 SELECT 구문의 SELECT절에 기술된 조회된 열의 개수와 자료형이 순서대로 일치해야 한다.
  • 컬럼의 헤더는 첫번째 구문에 기술된 이름이 출력
  • ORDER BY는 마지막에 한 번만 기술 가능하고 BLOB, CLOB, BFILE, LONG 타입은 사용할 수 없다.

1) SET연산자

  • UNION : 각 결과의 합(합집합 : 중복되는 값은 한번 출력)
  • UNION ALL : 각 결과의 합(합집합)
  • INTERSECT : 각 결과의 중복되는 부분만 출력(교집합)
  • EXCEPT : 첫번째 결과에서 두번째 결과를 뺌(차집합), 데이터 베이스 종류에 따라 MINUS라고도 함.

합집합

DEPT 테이블의 DEPTNO와 EMP 테이블의 DEPTNO 합쳐서 조회
-- UNION
SELECT DEPTNO
FROM DEPT
UNION
SELECT DEPTNO
FROM EMP;

-- UNION ALL
SELECT DEPTNO
FROM DEPT
UNION ALL
SELECT DEPTNO
FROM EMP;

교집합

-- INTERSECT
SELECT DEPTNO
FROM DEPT
INTERSECT
SELECT DEPTNO
FROM EMP;

차집합

-- EXCEPT
SELECT DEPTNO
FROM DEPT
EXCEPT
SELECT DEPTNO
FROM EMP;

2. Sub Query

서브 쿼리는 하나의 SQL 문장 안의 절에 포함된 SQL

  • 서브쿼리는 반드시 연산자 오른쪽에 기재해야 하고 반드시 괄호로 감싸야 한다

분류방법

  • FROM 절에 SELECT문을 이용해서 Sub Query를 만들면 인라인 뷰(View)
  • WHERE 절에 SELECT문을 이용해서 Sub Query를 만들면 서브 쿼리(Sub Query)
  • Sub Query 가 리턴하는 행의 개수에 따라 단일 행 Sub Query 와 다중 행 Sub Query로 나눈다
예시) MILLER의 부서명을 알아내기 위한 Sub Query문

SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO FROM EMP WHERE ENAME='MILLER' )

서브쿼리 먼저 해석하고 메인쿼리를 해석한다.

단일 행 sub Query

단일 행 서브쿼리는 수행 결과가 오직 하나의 행을 반환하는 서브쿼리이다. 하나의 행을 반환하기 때문에 단일행 비교 연산자인 =, >, <, >=, <=, <>를 사용하는 것이 가능하다

예시) tCity 테이블에서 popu가 최대인 name을 조회하기
-- 에러
SELECT MAX(POPU),NAME
FROM tCity

-- 에러
SELECT NAME, popu
FROM tCity
where popu = max(popu);

첫 문장이 에러인 이유 - 그룹함수는 그룹화하지 않은 컬럼과 같이 조회할 수 없음.
두번째 문장은 그룹 함수를 where 절에 사용해서 에러가 난다.

인구의 최대값을 먼저 구해서 조회한 뒤 인구수가 최대값고 동일한 데이터를 조회해야한다.

SELECT NAME, popu
FROM tCity
where popu = (select max(popu) from tcity);

예시) emp 테이블에서 평균 급여(sal)를 구하는 쿼리문을 sub query로 사용 하여 평균 급여보다 더 많은 급여를 받는 사원을 검색하는 문장
select ename, sal
from emp
where sal >= (select avg(sal) from emp);

연습문제

1. EMP 테이블에서 DEPT 테이블의 LOC가 DALLAS인 사원의 이름(ENAME), 부서 번호(DEPTNO)를 출력, emp 테이블과 dept 테이블은 deptno 컬럼이 같이 존재한다(외래키?)
select ename, deptno
from emp
where deptno = (select deptno from dept where lower(loc) = 'dallas');

다중열 Sub Query

다중열 서브쿼리는 서브쿼리의 결과가 여러개의 열인 경우이다.

예시) tstaff 테이블에서 name이 안중근인 데이터의 depart와 gender가 동일한 데이터를 조회
-- 단일 행 서브쿼리 이용
select *
from tstaff
where depart = (select depart from tstaff where name = '안중근') AND GENDER = (select GENDER from tstaff where name = '안중근')
-- 다중열 서브쿼리 이용, 여러 열을 한꺼번에 비교
select *
from tstaff
where (depart, gender) = (select depart, gender from tstaff where name = '안중근')

다중행 Sub Query

Sub Query에서 반환되는 결과가 하나 이상의 행일 때 사용하는 Sub Query
이 경우에는 단일 행 연산자를 사용하면 에러가 발생한다

다중행 연산자

IN : 목록에 포함되면 된다
ANY SOME : 하나라도 일치하면 된다
ALL : 전부 일치
EXIST : 하나라도 일치하면 참을 리턴한다

이때 ANY와 ALL은 MAX나 MIN 함수로 대체가 가능하다

IN 연산자

서브 쿼리가 여러개의 행을 반환할 때 그 중 하나의 값과 일치하는 데이터를 조회할 때는 IN 연산자 사용

예시) EMP 테이블에서 EMPNO, ENAME, SAL, DEPTNO를 조회하는데 SAL이 EMP 테이블의 각 부서(DEPTNO)의 최대 SAL 중 하나와 일치하는 데이터를 조회
-- in 연산자 사용
select empno, ename, sal, deptno
from emp
where sal in (select max(sal) from emp group by deptno);

ALL 연산자

메인쿼리의 비교조건이 서브쿼리의 검색 결과와 모든 값이 일치하면 TRUE
잘 해석하면 MAX나 MIN 함수로 대체 가능합니다

예시) DEPTNO가 30인 데이터의 SAL보다 전부 큰 SAL을 가진 데이터의 ENAME과 SAL을 조회
-- DEPTNO 가 30인 데이터의 SAL을 조회 : 6개 조회
SELECT SAL
FROM EMP
WHERE DEPTNO = 30

-- 위에서 검색된 데이터보다 전부 큰 SAL을 가진 데이터르 조회
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30);

-- ALL은 MAX보다 크면 되기 때문에 대체 가능하다
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

ANY 연산자

여러개의 데이터 중 하나만 조건을 만족하면 TRUE
ALL과 마찬가지로 MIN이나 MAX함수를 이용해서 대체가 가능하다

예제) DEPTNO가 30인 데이터의 SAL보다 하나라도 더 큰 SAL을 가진 데이터의 ENAME과 SAL을 조회
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30);

-- ANY은 MIN보다 크면 되기 때문에 대체 가능하다
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30);

예제) EMP 테이블에서 JOB이 SALESMAN 인 사원의 최소 SAL 보다 SAL이 많은 사원들의 ENAME 과 SAL, JOB를 출력하되 영업 사원(SALESMAN)은 출력하지 않도록 작성
select ename, sal, job
from emp
where (sal > (select min(sal) from emp where job = 'salesman')) and job <> 'salesman'

EXISTS 연산자

데이터의 존재 여부를 리턴
EXISTS의 구문이 참이 되면 전부 출력

예제) EMP 테이블에서 SAL이 2000이 넘는 사원이 있으면 ENAME과 SAL을 조회
SELECT ENAME, SAL
FROM EMP
WHERE EXISTS(SELECT 1 FROM EMP WHERE SAL >= 2000)

연습문제

  1. EMP 테이블에서 ENAME이 BLAKE 인 데이터와 같은 부서(DEPTNO)에 있는 모든 사원의 이름(ENAME)과 입사일자(HIREDATE)를 출력하는 SELECT문을 작성
SELECT ENAME, HIREDATE
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'BLAKE') AND ENAME <> 'BLAKE';

  1. EMP 테이블에서 평균 급여(SAL) 이상을 받는 모든 종업원에 대해서 종업원 번호(EMPNO)와 이름(ENAME)을 출력하는 SELECT문을 작성하시오. 단 급여가 많은 순으로 출력
SELECT EMPNO, ENAME
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY SAL DESC;

  1. EMP 테이블에서 이름(ENAME)에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 종업원에 대해 사원 번호(EMPNO),이름(ENAME),급여(SAL)를 출력하는 SELECT문을 작성하시오. 단 사원번호(EMPNO) 순으로 출력
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%')
ORDER BY EMPNO;

  1. EMP 테이블에서 DEPT 테이블의 LOC 가 DALLAS인 종업원에 대해 이름(ENAME),업무(job), 급여(SAL)를 출력하는 SELECT문을 작성
select ename, JOB,SAL
from emp
where deptno = (select deptno from dept where lower(loc) = 'dallas');
  1. EMP 테이블에서 MGR의 이름(ENAME)이 KING 인 사원의 이름(ENAME)과 급여(SAL)를 출력하는 SELECT문을 작성
-- MGR = 관리자 사원번호 - MANAGER의 MGR : 7839
SELECT ENAME, SAL
FROM EMP
WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING');

  1. EMP 테이블에서 월급(SAL)이 DEPTNO가 30인 데이터의 최저 월급보다 높은 사원의 모든 정보를 출력하는 SELECT문을 작성
SELECT *
FROM EMP
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30);

JOIN

  • 2개의 테이블을 가로 방향으로 합치는 연산
  • 2개 이상의 테이블로부터 여러개의 컬럼을 추출하고자 할 때 사용

조인의 종류

  • CROSS JOIN : 2개 테이블의 모든 조합, Cartesian Product 라고도 함
  • EQUI JOIN : 동일한 의미를 갖는 컬럼의 값이 같을 때 조인
  • NON - EQUI JOIN : 동일한 의미를 갖는 컬럼에 상관없이 = 이 외의 연산자를 이용해서 조인
  • OUTER JOIN : 어느 한쪽 테이블에만 존재하는 데이터도 조인에 참여
  • SELF JOIN : 동일한 테이블을 가지고 조인
  • SEMI JOIN : 서브쿼리를 이용해서 조인

테이블 구조

EMPT 테이블과 DEPT 테이블은 부서번호인 DEPTNO 컬럼을 모두 가지고 있다.

1. CROSS JOIN

  • X
  • Catesian Product 라고도 하는데 별다른 조인 조건 없이 from절에 2개 테이블 이상을 나열하는 경우 발생
  • 2개 테이블의 모든 조합이 나오게 된다.

emp 테이블

데이터 14개
열 8개

dept 테이블

데이터 4개
열 3개

cross join 형식

EMP X DEPT

select *
from emp,dept;

데이터 56개 = 14 * 4
열 11개 = 8 + 3

결과는 모든 데이터의 조합이 나오므로 행의 개수는 곱한 것과 같고, 열의 개수는 더한 것과 같다.

2. EQUI JOIN

  • 2개의 테이블에 동일한 의미를 갖는 컬럼이 있는경우 두 개의 컬럼이 같은 값을 갖는 데이터만 조인한다.
  • WHERE 절에 조인 조건을 기술
  • 컬럼의 이름이 동일한 경우 테이블이름.컬럼이름으로 조인조건을 기술해야 한다

EQUI JOIN 기본 형식

SELECT *
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

데이터 14개, 열 11개

DEPTNO가 일치하는 애들끼리 매칭되어서 JOIN 된다.

조건 절에 추가 가능

ENAME이 MILLER 인 사원의 정보만 추출
SELECT *
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND ENAME = 'MILLER';
원하는 조건만 알고 싶을 때(이름, 부서이름)
SELECT ENAME, DNAME
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

주의할 점

  • 두 개의 테이블에 동일한 이름의 컬럼을 사용할 때는 테이블 이름.컬럼으로 기술해야 한다. 그렇지 않으면 애매한 컬럼이름이라고 에러가 발생한다.

테이블에 다른 이름 부여

테이블 이름을 변경해서 사용할 수 있는데 FROM 절 다음에 테이블 이름을 명시하고 공백을 둔 다음에 다른 이름을 지정 - EMP E 별명이 아니라 테이블 이름이 바뀐 것이기 때문에 이후 절에서는 테이블 이름을 사용할 때 다른 이름을 사용해야한다.

SELECT *
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;

연습문제

1. DEPT 테이블의 LOC가 ‘NEW YORK’ 인 사원의 EMP 테이블의 이름(ENAME)과 급여(SAL)를 조회
SELECT ENAME, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'NEW YORK';

-- 서브쿼리 이용
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'NEW YORK');

조회하고자 하는 컬럼들이 하나의 테이블에 존재하는 경우는 조인 대신 서브쿼리 이용이 가능하고 서브쿼리를 이용하는 것이 효율적이다. 조인을 하면 테이블이 커지기 때문에 조인은 회피하는 것이 좋다. 대신 조인을 하지 않게되면 쿼리문이 길어진다.

2. DEPT 테이블의 DNAME 컬럼의 값이 ‘ACCOUNTING’ 인 사원의 EMP 테이블의 이름(ENAME)과 입사일(HIREDATE)을 조회
SELECT ENAME, HIREDATE
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND DNAME = 'ACCOUNTING';

3. NON EQUI JOIN

  • = 이외의 연산자를 가지고 조인하는 것

SALGRADE 테이블,EMP 테이블을 조회

SELECT *
FROM SALGRADE;

SELECT *
FROM EMP;

두 테이블의 값이 같은걸 찾는 게 아닌 특정 범위 내에 잇는지 조사하기 위함이다.
이 경우에는 SAL이 LOSAL과 HISAL 사이에 있는 조건을 가지고 조회를 해야한다.
이렇게 다른 테이블의 데이터와 = 이 아닌 다른 연산자로 비교하는 경우는 NON EQUI JOIN 이라고 한다.

SELECT ENAME, SAL, GRADE
FROM SALGRADE, EMP
where SAL between LOSAL and HISAL;

4. SELF JOIN

  • 하나의 테이블을 가지고 JOIN
  • 하나의 테이블에 동일한 의미를 갖는 컬럼이 2개 이상 존재하는 경우 사용
  • 동일한 테이블을 2번 이용하기 때문에 반드시 테이블에 새로운 이름을 부여해야 한다.
  • EMP 테이블에서 EMPNO 는 사원번호이고 MGR은 관리자 사원번호이다.
    이때 사원 이름과 관리자의 이름을 동시에 조회하고자 하는 경우 관리자 사원번호를 조회한 후 다시 이를 이용해서 관리자 사원 이름을 조회해야 하는데 이경우 SELF JOIN이 필요하다
예제) 사원이름과 관리자 이름 조회
SELECT CONCAT(E.ENAME, '의 매니저는', M.ENAME)
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;

예제) EMP 테이블에서 MANAGER가 KING인 사원들의 ENAME과 JOB을 조회
SELECT E.ENAME, E.JOB
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO AND M.ENAME = 'KING';

5. ANSI JOIN

  • ANSI 표준은 단순 조인을 크로스 조인으로 정의하여 공식화해 두었는데 이름만 다를 뿐 같은 조인이다
  • 콤마 대신 CROSS JOIN 이라고 쓰면된다
-- CROSS JOIN
FROM 테이블이름, 테이블이름

-- ANSI JOIN
FROM 테이블이름 CROSS JOIN 테이블이름

6. ANSI INNER JOIN

  • EQUI JOIN과 결과가 동일하다

기본형식

FROM 테이블 이름 INNER JOIN 테이블이름
ON 조인조건
예시) ENAME 이 MILLER인 사원의 ENAME(EMP)와 DNAME(DEPT) 조회
SELECT ENAME, DNAME
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO 
WHERE ENAME = 'MILLER';

  • 2개 테이블의 조인 컬럼이 같은 경우는 ON 대신에 USING (컬럼이름) 을 사용해도 된다
SELECT ENAME, DNAME
FROM EMP INNER JOIN DEPT
USING (DEPTNO) 
WHERE ENAME = 'MILLER';

7. NATURAL JOIN

  • 2개 테이블의 조인 컬럼의 이름이 같은 경우 INNER 대신에 NATURAL 이라고 기재하고 조인 조건을 생략

기본형식

SELECT *
FROM 테이블 이름 NATURAL JOIN 테이블이름
예시)
SELECT ENAME, DNAME
FROM EMP NATURAL JOIN DEPT 
WHERE ENAME = 'MILLER';

8. OUTER JOIN

  • 어느 한쪽 테이블에만 존재하는 데이터도 JOIN에 참여하는 것
  • INNER JOIN이나 EQUI JOIN은 양쪽 테이블에 모두 존재하는 데이터가 JOIN에 참여
  • ANSI JOIN 에서는 3가지 JOIN 조건을 제시
    • LEFT : 왼쪽에만 존재하는 데이터도 참여
    • RIGHT : 오른쪽 테이블에만 존재하는 데이터도 참여
    • FULL : 양쪽에 존재하는 모든 데이터 참여

기본형식

SELECT *
FROM 테이블 이름 [LEFT | RIGHT | FULL] OUTER JOIN 테이블이름
예시) INNER JOIN, OUTER JOIN 비교

EMP 테이블 - DEPTNO 10 20 30
DEPT 테이블 - DEPTNO 10 20 30 40

INNER JOIN 결과 - DEPTNO 10 20 30

select *
FROM EMP RIGHT OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

OUTER JOIN 결과 - DEPTNO 10 20 30 40

MariaDB에는 FULL OUTER JOIN이 없기 때문에 SET 연산을 이용해서 FULL OUTER JOIN을 구현한다. 

select *
FROM EMP LEFT OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
UNION
select *
FROM EMP RIGHT OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

연습문제

  1. EMP 테이블에서 모든 사원에 대한 이름(ename), 부서번호(deptno) DEPT 테이블에서 부서명(dname)을 가져와서 출력하는 SELECT 문장을 작성 – 2개의 테이블에는 DEPTNO 가 같이 존재
-- EQUI JOIN
SELECT E.ENAME, E.DEPTNO,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;

  1. DEPT 테이블의 LOC가 NEW YORK에서 근무하고 있는 사원에 대하여 EMP 테이블의 이름(ename), 업무(job), 급여(sal), DEPT 테이블의 부서명(dname)을 출력하는 SELECT 문장을 작성
-- EQUI JOIN
SELECT E.ENAME, E.JOB, E.SAL, D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND D.LOC = 'NEW YORK';

  1. EMP 테이블에서 보너스(comm)가 null 이 아닌 사원에 대하여 이름(ename), DEPT 테이블의 부서명(dname), 위치(loc)를 출력하는 SELECT 문장을 작성
-- INNER JOIN
SELECT E.ENAME, D.DNAME, D.LOC
FROM EMP E INNER JOIN DEPT D
USING (DEPTNO)
WHERE E.COMM IS NOT NULL;

  1. EMP 테이블에서 이름(ename) 중 L자가 있는 사원에 대하여 이름(ename), 업무(job), DEPT 테이블의 부서명(dname), 위치(loc)를 출력하는 SELECT 문장을 작성
-- INNER JOIN
SELECT E.ENAME,E.JOB, D.DNAME, D.LOC
FROM EMP E INNER JOIN DEPT D
USING (DEPTNO)
WHERE E.ENAME LIKE '%L%';

  1. EMP 테이블에서 그들의 관리자(mgr) 보다 먼저 입사한 사원에 대하여 이름(ename), 입사일(hiredate), 관리자(mgr) 이름, 관리자(mgr) 입사일을 출력하는 SELECT 문장을 작성
-- SELF JOIN
SELECT E.ENAME, E.HIREDATE, M.ENAME, M.HIREDATE
FROM EMP E,EMP M
WHERE E.MGR = M.EMPNO AND M.HIREDATE > E.HIREDATE;

profile
JIWON
이전 포스트

[MariaDB] 5. Grouping

다음 포스트

[MariaDB] 7. 모델링, 정규화

0개의 댓글