서브쿼리

서브 쿼리란, 부질의, 하위 쿼리라고도 불리며 SELECT문, CREATE문, INSERT문, UPDATE문, DELETE문 등의 내부에서 사용하는 SELECT문을 말합니다.

SELECT 문에서 서브쿼리

SELECT절, FROM절, WHRER절, HAVING 절 등 에서 사용할 수 있습니다.

SELECT 절

스칼라 서브쿼리라고도 하며, 서브쿼리의 결과가 반드시 한 행 & 한 열의 결과셋만 반환해야합니다.

-- 사용법
SELECT 컬럼1 (SELECT 컬럼2 FROM 테이블2 WHERE 테이블2.컬럼1 = 테이블1.컬럼1)
FROM 테이블1
;

-- 사용예시
SELECT 
	memberId
	, NAME
	, (
    	SELECT orderNo 
        FROM tbl_orderinfo 
        WHERE tbl_member.memberId = tbl_orderinfo.memberId 
        LIMIT 1
    ) AS orderinfo
FROM tbl_member;

FROM 절

FROM절에서 사용하는 서브쿼리는 인라인 뷰 라고도 부르며, 서브쿼리로 임의적으로 가상의 테이블(인라인 뷰)을 만들고 해당 가상의 테이블에서 데이터를 가져오는 사용법 입니다.

서브쿼리로 인라인 뷰를 만들 때는 서브쿼리 작성 후 AS로 alias명을 작성해줘야 인라인 뷰로 인식할 수 있습니다.

-- 사용법
SELECT 컬럼1, 컬럼2
FROM (
	SELECT 컬럼1, 컬럼2 FROM 테이블1
) AS alias명;

-- 사용예시
SELECT 
	memberId, name, orderNo, orderAmount
FROM (
	SELECT 
		MB.memberId, MB.name, OI.orderNo, OI.orderAmount
	FROM tbl_member AS MB
	INNER JOIN tbl_orderinfo AS OI ON OI.memberId = MB.memberId
) AS IV
WHERE orderAmount > 10000;

WHERE 절

SELECT문에서는 주로 WHERE 조건절에서 서브쿼리를 사용하고는 합니다.

• 단일행 서브쿼리 : 서브쿼리의 결과(행)이 하나만 전달되는 서브쿼리 입니다.
• 다중행 서브쿼리 : 서브쿼리의 결과(행)이 여러개인 서브쿼리로 >, < 등의 비교 연산자를 사용할 경우 에러가 납니다.
  다중행 연산자인 IN / ANY / ALL / EXISTS 등의 키워드를 이용해서 작성해야합니다.

  -- 사용예시
  # 단일행 서브쿼리
  SELECT *
  FROM tbl_member
  WHERE memberNo = (
    SELECT memberId FROM tbl_orderInfo
    WHERE orderNo = '202302001'   # 결과가 단일행인 서브쿼리
  );
  
  # 다중행 서브쿼리
  SELECT *
  FROM tbl_member
  WHERE memberId = ( # 서브 쿼리 결과가 다중행으로 나와 오류 발생 (SQL 오류 (1242): Subquery returns more than 1 row)
    SELECT memberId FROM tbl_orderInfo
    WHERE orderAmount > 10000  
  );
  
  SELECT *
  FROM tbl_member
  WHERE memberId IN ( # 다중행에 대해 포함여부를 확인하는 IN 키워드를 사용하여 정상작동
    SELECT memberId FROM tbl_orderInfo
    WHERE orderAmount > 10000  
  );

💡 IN / ALL / ANY / EXISTS

• IN
  대상 IN (다중결과) 형식으로 사용하며, 다중결과 내 해당 대상의 값이 1개라도 있으면 TRUE를 반환하고 없으면 FALSE를 반환합니다.
• ALL
  대상 비교연산자 ALL (다중결과)형식으로 사용하며, 다중결과 내 값에 대해 모두 비교연산자 조건을 충족시켜야 1(TURE)을 반환하고, 하나라도 충족하지 못할 시 0(FALSE)을 반환합니다.

  SELECT jobCode > ALL ( 
  	SELECT jobCode FROM tbl_member
  );  
  # 서브쿼리 절에서 나온 모든 행의 대해 > 비교조건을 충족해야 1(TURE) / 하나라도 미충족 시 0(FALSE)를 반환

• ANY
  대상 비교연산자 ANY (다중결과)형식으로 사용하며, 다중결과 내 값에 대해 하나라도 비교연산자 조건을 충족할 시 1(TURE)을 반환하고, 전부 충족하지 못할 시 0(FALSE)을 반환합니다.

  SELECT jobCode > ANY ( 
  	SELECT jobCode FROM tbl_member
  );  
  # 서브쿼리 절에서 나온 행의 대해 > 하나라도 비교조건을 충족해야 1(TURE) / 모두 미충족 시 0(FALSE)를 반환

• EXISTS
  서브쿼리를 WHERE절에 사용할 때 주로 사용하며 해당 행이 다중행에 존재여부를 확인 후 있으면 1(TRUE)을 반환하고 없으면 0(FALSE)을 반환합니다.

  SELECT 
  	memberId
  FROM tbl_member
  WHERE EXISTS (
  	SELECT * FROM tbl_orderinfo
  	WHERE tbl_member.memberId = tbl_orderinfo.memberId
  );

HAVING 절

WHERE절에서 사용할 때와 사용법은 동일합니다.
다만, HAVING절에서 사용할 때는 GROUP BY로 그룹핑한 결과에 대해 집계 함수 등을 이용해서 부가적인 조건을 걸기 위한 목적으로 사용됩니다.

CREATE 문에서 서브쿼리

CREATE문에서 테이블 생성하면서 동시에 특정 테이블에 있는 데이터를 복사하여 값을 넣을 수 있습니다.

-- 사용법
# 필요한 컬럼만 생성 후 특정 컬럼만 갖고오는 경우
CREATE TABLE 테이블명(
	컬럼명1 타입 ~~,
    컬럼명1 타입 ~~,
    ...
)
SELECT 컬럼명,,, FROM 복사해올 테이블명;

# 테이블 구조부터 값까지 싹 다 복사해오는 경우
CREATE TABLE 테이블명
SELECT 컬럼명,,, FROM 복사해올 테이블명;
# 대상 테이블과 복사해올 테이블의 컬럼 구조가 똑같다면 ALL / * 으로 사용할 수 있습니다.

-- 사용예시
# 필요한 컬럼만 생성 후 특정 컬럼만 갖고오는 경우
CREATE TABLE tbl_member_copy(
	memId varchar(20) null comment '회원 아이디',
    memName varchar(20) null comment '회원 이름' 
)
SELECT memberID, name FROM tbl_member;

# 테이블 구조부터 값까지 싹 다 복사해오는 경우
CREATE TABLE tbl_member_copy_all
SELECT * FROM tbl_member;

INSERT 문에서 서브쿼리

INSERT문에서 서브쿼리를 사용할 때는 기본 INSERT 문에서 VALUES절 대신에 서브쿼리를 입력해주는 방식으로 사용할 수 있습니다.

-- 사용법 
# 필요한 컬럼만 갖고오는 경우
INSERT INTO 테이블명(컬러명,,,)
SELECT 컬럼명,,, FROM 복사해올 테이블명

# 컬럼구조가 전부 동일할 경우
INSERT INTO 테이블명        # 컬럼구조 & 순서까지 똑같은 경우 컬럼명 나열도 생략
SELECT * FROM 테이블명
# 대상 테이블과 복사해올 테이블의 컬럼 구조가 똑같다면 ALL / * 으로 사용할 수 있습니다.

-- 사용예시
# 필요한 컬럼만 갖고오는 경우
INSERT INTO tbl_customer (custid, custnm)
SELECT memberID, NAME FROM tbl_member;

# 컬럼구조가 전부 동일한 경우
INSERT INTO tbl_member_copy
SELECT * FROM tbl_member;

UPDATE 문에서 서브쿼리

UPDATE문에서 서브쿼리를 사용할 때는 기본 UPDATE문에서 SET절 또는 WHERE절 에서 사용할 수 있습니다.
SET 절에서 서브쿼리를 사용할 때는 값을 동적으로 업데이트 해주기 위한 용도로 주로 사용됩니다.

-- 사용법
# SET절에서 사용하는 경우
UPDATE 테이블1
SET 컬럼2 = (SELECT 컬럼2 FROM 테이블2 WHERE 테이블2.컬럼1 = 테이블1.컬럼1);

# WHERE절에서 사용하는 경우
UPDATE 테이블1
SET 컬럼2 = 값,,,
WHERE 컬럼1 비교연산자 ( # 단일행 서브쿼리 또는 다중행 연산자를 사용하고 다중행 서브쿼리 사용가능
	SELECT 컬럼1 FROM 테이블2;
);
# WHERE 절에서 사용할 때는 위 SELEFT문 내 WHERE절에서 사용할 때와 사용법이 동일합니다.

-- 사용예시
# SET절에서 사용하는 경우
UPDATE tbl_member_copy AS copy
SET name = (SELECT name FROM tbl_member AS origin WHERE origin.memberId = copy.memberId);

# WHERE절에서 사용예시는 SELEFT문 내 WHERE절과 동일하므로 생략합니다.

DELETE 문에서 서브쿼리

DELETE문에서 서브쿼리를 사용할 때는 WHERE절에서 사용할 수 있으며, 사용법은 SELECT문 내 WHERE절과 동일합니다!

주로 동적으로 데이터를 삭제해줄 때 사용됩니다.

참고

MariaDB로 따라 하며 배우는 SQL프로그래밍 데이터베이스 기초에서 실무까지 - 나익수, 서연경 지음
위 책을 공부하며 작성하고 있습니다!