[SQL] : Subquery (3) / Subquery, 중첩 서브쿼리 (Nested Subquery)

(1) Subquery
(2) 중첩 서브쿼리 (Nested Subquery)
(3) Single Row 서브쿼리 (Single Row)
(4) Multiple Row 서브쿼리 (Multiple Row Subquery)
(5) Multiple Column 서브쿼리 (Multiple Column Subquery)

(1) Subquery

• Subquery는 하나의 SQL 쿼리문 안에 포함되어 있는 또 다른 SQL 쿼리문을 말한다. 메인쿼리가 서브쿼리를 포함하는 종속적인 관계로 서브쿼리는 복잡한 데이터 검색이나 특정 조건에 맞는 데이터를 추출할 때 사용된다.

• 서브쿼리는 메인쿼리의 칼럼 사용이 가능하다. 서브쿼리는 메인쿼리 내의 쿼리에서 실행되며 메인쿼리의 칼럼을 참조하여 조건을 설정하거나 연산을 수행할 수 있다.

• 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다. 메인쿼리는 서브쿼리의 내부에서 정의된 칼럼이나 값을 직접 참조할 수 없다는 것이다.

• Subquery 는 메인쿼리 내에서 괄호로 묶어서 사용해야 하며 단일 행 혹은 복수 행 비교 연산자와 함께 사용이 가능하다.

• subquery에서는 값을 정렬하는 order by를 사용할 수 없다.

• Subquery의 종류로는 Scalar Subquery, 인라인 뷰(Inline View), 중첩 서브쿼리 (Nested Subquery) 가 있다.

• 중첩 서브쿼리에서 Subquery는 메인 메인쿼리의 컬럼을 가지고와서 사용할 수 있다.

(2) 중첩 서브쿼리 (Nested Subquery)

• 중첩 서브쿼리 (Nested Subquery)는 SQL 쿼리 안에 다른 쿼리를 중첩하여 사용하는 서브쿼리 종류 중 하나다. 일반적으로 외부 쿼리에 대한 결과를 계산하기 위해 내부 쿼리가 먼저 실행되는데 중첩 서브쿼리의 이 내부 쿼리는 외부 쿼리의 조건에 따라 실행되며, 그 결과가 외부 쿼리의 조건이나 비교 연산에 사용될 수 있다.

• 중첩 서브쿼리에서 Subquery는 메인 메인쿼리의 컬럼을 가지고와서 사용할 수 있다.

• WHERE 절에서 사용하는 서브쿼리로 중첩 서브쿼리 내에서도 다양한 방법이 있다.

• Single Row - 하나의 열을 검색하는 서브쿼리
• Multiple Row - 하나 이상의 열을 검색하는 서브쿼리
• Multiple Column - 하나 이상의 행을 검색하는 서브쿼리

(3) Single Row 서브쿼리 (Single Row)

• Single Row 서브쿼리는 단일 행 서브쿼리로 하나의 열(칼럼)을 검색하고 반환하는 것이다.

• 이 서브쿼리는 주로 메인 쿼리에서 비교 연산자의 오른쪽에 사용되어 서브쿼리가 비교연산자( =, >, >=, <, <=, <>, !=) 와 사용되는 경우 서브쿼리의 검색 결과는 한 개의 결과값을 가져야 한다. 그래서 서브쿼리는 단일 값을 반환하지만 버스쿼리가 반환한 단일 값을 기준으로 메인 쿼리에서 조건을 적용할 때 여러 행이 선택되어 출력되는 모습도 볼 수 있다. 즉 single row subquery는 서브쿼리가 단일 행 값을 출력한다는 것이다.

🖍️ Single Row 서브쿼리 예제

mysql > SELECT name FROM celeb 
	    WHERE name = (SELECT host FROM snl_show WHERE id = 1);
+---------- +
| name      |
+---------- +
| 강동원     |
+---------- +

• single row 서브쿼리는 where 절에서 사용했을 때 단일 값, 즉 한 개의 값만 반환해야 하기에 조건문 where 에서 단일 값을 꼭 지정해주어야 한다.

(4) Multiple Row 서브쿼리 (Multiple Row)

• Multiple Row 서브쿼리는 다중 행 서브쿼리로 하나 이상의 열(칼럼)을 검색하고 반환하는 것이다.

• 이 서브쿼리는 주로 where 절과 함께IN, EXITS, ANY, ALL 등의 연산자와 함께 사용된다.

🖍️ Multiple Row 서브쿼리 IN 예제

• IN 연산자는 서브쿼리의 결과 집합 안에 메인 쿼리의 값이 포함되어 있는지 여부를 검사하는 데 사용된다. 그래서 IN 연산자는 서브쿼리에서 반환된 값들 중에 메인 쿼리의 열 값이 하나라도 포함되어 있으면 조건을 만족시킨다.

# SNL 에 출연한 영화배우를 조회
mysql > SELECT host
	    FROM snl_show
		WHERE host IN (SELECT name
					   FROM celeb
                       WHERE JOB_TITLE LIKE '%영화배우%');
+---------- +
| name      |
+---------- +
| 강동원     |
| 차승원     |
+---------- +

• snl_show 테이블에서 host 컬럼의 값들을 선택하는 쿼리이다. 이때 선택된 값들은 celeb 테이블에서 JOB_TITLE 컬럼 값이 '%영화배우%'에 해당하는 값을 가진 경우에 해당하는 host 컬럼 값들로 한정되어 출력되는 것이다.

🖍️ Multiple Row 서브쿼리 EXITS 예제

• EXISTS 절은 서브쿼리가 하나 이상의 결과를 반환하는 경우에 서브쿼리에 데이터가 존재하는지 체크하고 참(true)을 반환하며, 이에 따라 바깥 쿼리의 조건을 만족시키게 된다.

# 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회

mysql > SELECT name
	    FROM police_station p
		WHERE EXISTS (SELECT police_station
					   FROM crime_status c
                       WHERE p.name = c.reference AND case_number > 2000);

• WHERE EXISTS (...): 바깥 쿼리에서는 서브쿼리의 결과에 따라 결과를 필터링한다. EXISTS 절은 서브쿼리가 하나 이상의 결과를 반환하는 경우에 참(true)을 반환하며, 이에 따라 바깥 쿼리의 조건을 만족시켜 값을 출력한다.

• SELECT police_station FROM crime_status c: 서브쿼리는 crime_status 테이블에서 police_station 값을 선택한다. 이는 범죄 검거 혹은 발생의 값을 나타내는 경찰서를 나타낸다.

• WHERE p.name = c.reference AND case_number > 2000: 서브쿼리의 WHERE 절에서 p.name = c.reference 조건을 사용하여 police_station 테이블의 name 열과 crime_status 테이블의 reference 열 중 같은 값을 연결한다. 그리고 AND 연산자를 추가해서 case_number > 2000 으로 범죄 발생 건수가 2000보다 큰 경우를 필터링하고 값을 출력한다.

출력 결과

+----------------------- +
| name                   |
+----------------------- +
| 서울강남경찰서            |
| 서울강서경찰서            |
| 서울관악경찰서            |
| 서울구로경찰서            |
| 서울노원경찰서            |
I 서울송파경찰서            |
I 서울영등포경찰서          |
I 서울중랑경찰서            |
+----------------------- +

🖍️ Multiple Row 서브쿼리 ANY 예제

• ANY 절은 서브쿼리가 하나 이상의 결과를 반환하는 경우에 서브쿼리에 데이터가 존재하는지 체크하고 서브쿼리 결과 중에 최소한 하나라도 만족하는 값을 반환한다.

# SNL 에 출연한 적이 있는 연예인 이름 조회

mysql > SELECT name
	    FROM celeb
		WHERE  name = ANY (SELECT host
					   	   FROM snl_show);
                       
+---------- +
| name      |
+---------- +
| 강동원     |
| 유재석     |
| 차승원     |
| 이수현     |
+---------- +

• name = ANY (...): ANY를 사용해 celeb 테이블의 name 컬럼 값이 서브쿼리의 결과 중 하나와 동일한 경우를 선택하는 것이다.

• SELECT host FROM snl_show: 서브쿼리는 snl_show 테이블에서 host 컬럼 값을 선택하게 된다. 이 테이블은 SNL 쇼의 호스트 정보를 담고 있다. 즉 snl 쇼의 호스트 이름과 celeb 테이블의 연예인 이름이 같은 결과를 찾아 snl에 출연한 적이 있는 연예인 이름을 조회하는 것이다.

🖍️ Multiple Row 서브쿼리 ALL 예제

• ALL 절은 서브쿼리가 하나 이상의 결과를 반환하는 경우에 서브쿼리에 데이터가 존재하는지 체크하고 서브쿼리 결과 중에 모두 만족하는 조건의 값을 반환한다.

# SNL 에 출연한 적이 있는 연예인 이름 조회

mysql > SELECT name
	    FROM celeb
		WHERE  name = ALL (SELECT host
					   	   FROM snl_show
                           WHERE id = 1));
                       
+---------- +
| name      |
+---------- +
| 강동원     |
+---------- +

• celeb: 메인 쿼리에서는 celeb 테이블을 사용한다.

• name = ALL (...): ALL를 사용해 celeb 테이블의 name 컬럼 값이 서브쿼리의 결과와 모두 일치해야 하는 경우를 선택한다.

• SELECT host FROM snl_show WHERE id = 1: 서브쿼리는 snl_show 테이블에서 id가 1인 경우에 해당하는host 컬럼 값을 선택하게 된다. 이 테이블은 SNL 쇼의 호스트 정보를 담고 있다. 즉 snl 쇼의 호스트 이름과 celeb 테이블의 연예인 이름이 같은 결과를 찾아 id가 열의 값이 1인 것을 찾는 것이다.

(5) Multiple Column 서브쿼리 (Multiple Column Subquery)

• Multiple Column 서브쿼리 는 연관 서브쿼리라고도 하며 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우를 말하며 컬럼을 여러 개 조회하여 반환하고 출력하는 것을 말한다.

🖍️ Multiple Column 서브쿼리 예제

# 강동원과 성별，소속사가 같은 연예인의 이름，성별，소속사를 조회

mysql > SELECT name, sex, agency
	    FROM celeb
		WHERE (sex, agency) IN (SELECT sex, agency FROM celeb WHERE name = '강동원');
                       
+---------+-----+---------------+
| name    | sex | agency        |
+---------+-----+---------------+
| 강동원   | M   | YG엔터테인먼트  |
| 차승원   | M   | YG엔터테인먼트  |
+---------+-----+---------------+