SQL Subquery

1. SQL Subquery

About Subquery

• 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다.
• 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
• 서브쿼리는 메인쿼리의 칼럼 사용 가능
• 메인쿼리는 서브쿼리의 칼럼 사용 불가

Subquery 사용시 주의

• Subquery 는 괄호로 묶어서 사용
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
• subquery에서는 order by를 사용X

Subquery 종류

• 스카라 서브쿼리 (Scalar Subquery) - SELECT 절에 사용
• 인라인 뷰 (Inline View) - FROM 절에 사용
• 중첩 서브쿼리 (Nested Subquery) - WHERE 절에 사용

2. 스칼라 서브쿼리 (Scalar Subquery)

• SELECT 절에서 사용하는 서브쿼리. 결과는 하나의 Column 이어야 한다.

• 문법

SELECT column1, (SELECT column2 FROM table2 WHERE condition)
FROM table1
WHERE condition;

• 예제 : 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회

SELECT case_number,
	  (SELECT avg(case_number)
       FROM crime_status
       WHERE crime_type LIKE '강도' AND crime_type LIKE '검거') avg
FROM crime_status
WHERE police_station LIKE '은평' AND crime_type LIKE '강도' status_type LIKE '검거';

3. 인라인 뷰 (Inline View)

• FROM 절에 사용하는 서브쿼리로 메인쿼리에서는 인라인 뷰에서 조회한 Column만 사용가능

• 문법

SELECT a.column, b.column
FROM table1 a, (SELECT column1, column2 FROM table2)b
WHERE condition;

• 예제 : 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회

SELECT c.police_station, c.crime_type, c.case_number
FROM crime_status c,
	 (SELECT police_station, max(case_number) count
      FROM crime_status
      WHERE status_type LIKE '발생'
      GROUP BY police_station) m
WHERE c.police_station = m.police_station
  AND c.case_number = m.count; 

4. 중첩 서브쿼리 (Nested Subquery)

• WHERE 절에서 사용하는 서브쿼리.
  • Single Row - 하나의 열을 검색하는 서브쿼리
  • Multiple Row - 하나 이상의 열을 검색하는 서브쿼리
  • Multiple Column - 하나 이상의 행을 검색하는 서브쿼리

Single Row Subquery

• 서브쿼리가 비교연산자( =, >, >=, <, <=, <>, !=)와 사용되는 경우, 서브쿼리의 검색 결과는 한 개의 결과값을 가져야 한다. (두개 이상인 경우 에러)
• 문법

SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_name
					 FROM table_name
                     WHERE condition)
ORDER BY column_name;

• 예제

SELECT name
FROM celeb
WHERE name = (SELECT host 
			  FROM snl_show
			  WHERE id=1);

• 예제(에러) : 괄호 생략

SELECT name 
FROM celeb
WHERE name = SELECT host FROM snl_show ;

• 예제(에러) : 결과값 2개 이상

SELECT name 
FROM celeb
WHERE name = (SELECT host FROM snl_show);

Multiple Row Subquery

IN

• 서브쿼리 결과 중에 포함 될때
• 문법

SELECT column_names
FROM table_name
WHERE column_name IN (SELECT column_name
					  FROM table_name
                      WHERE condition)
ORDER BY column_names;

• 예제 : SNL에 출연한 영화배우를 조회

SELECT host
FROM snl_show
WHERE host IN (SELECT name
			   FROM celeb
               WHERE job_title LIKE '%영화배우%');

EXISTS

• 서브쿼리 결과에 값이 있으면 반환
• 문법

SELECT column_names
FROM table_name
WHERE EXISTS (SELECT column_name
			  FROM table_name
              WHERE condition)
ORDER BY column_names;

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

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

ANY

• 서브쿼리 결과 중에 최소한 하나라도 만족하면 (비교연산자 사용)
• 문법

SELECT column_names
FROM table_name
WHERE column_names = ANY (SELECT column_name
			  			  FROM table_name
              			  WHERE condition)
ORDER BY column_names;

• 예제 : SNL 에 출연한 적이 있는 연예인 이름 조회

SELECT name
FROM celeb
WHERE name = ANY (SELECT host
			 	  FROM snl_show);

ALL

• 서브쿼리 결과를 모두 만족하면 (비교 연산자 사용)
• 문법

SELECT column_names
FROM table_name
WHERE column_name = ALL (SELECT column_name
			  			 FROM table_name
             			 WHERE condition)
ORDER BY column_names;

• 예제

SELECT name
FROM celeb 
WHERE name = ALL (SELECT host
			  	  FROM snl_show
              	  WHERE id = 1);

Multiple Column Subquery

• 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우.
• 문법

SELECT column_names
FROM table_name a
WHERE (a.column1, a.column2, ...) IN (SELECT b.column1, b.column2, ...
					  				  FROM table_name
                      				  WHERE a.column_name = b.column_name)
ORDER BY column_names;

• 예제 : 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회

SELECT name, sex, agency
FROM celeb
WHERE (sex, agency) IN (SELECT sex, agency
			   			FROM celeb
               			WHERE name = '강동원');