1.서브 쿼리

하나의 SQL Query 내부에 또 다른 SQL Query가 중첩(Nesting) 된 형태의 Query를 의미

Main Query보다 먼저 실행되며 SubQuery역시 하나의 Query이기 때문에 기본적으로 SQL을 실행하였을 때 출력되는 결과 셋(Result set)을 반환함

customer 테이블에서 customer_id열의 최대값 조회

sql


SELECT MAX(customer_id) FROM customer;

customer 테이블에서 customer_id가 최대값인 회원의 customer_id, last_name, first_name 조회

sql


SELECT customer_id, last_name, first_name FROM customer WHERE customer_id = 599;

SubQuery를 활용하여 표현식 부분에 기술

sql


SELECT MAX(customer_id), first_name, last_name 
FROM customer;

sql


SELECT customer_id, first_name, last_name 
FROM customer 
WHERE customer_id = (SELECT MAX(customer_id) FROM customer);

→ Subquery를 쓰지 않으면, customer_id의 최대값을 먼저 찾는 Query를 실행해서 최대값을 가지는 customer_id를 조회하고, 그 뒤에 customer 테이블에서 원하는 데이터를 검색해야하기 때문에 2번의 Query가 필요하지만, Subquery를 활용할 경우 하나의 Query(단일 쿼리)로 실행 가능

SubQuery의 종류

상관 서브쿼리(Correlated subquery) - Main query의 열을 참조하는 쿼리
비상관 서브쿼리(Noncorrelated subquery) - Main query의 참조 없이 독립적으로 실행이 가능한 쿼리(주로 사용)

비상관 서브쿼리는 일반적인 연산자(=, <>, <, >, ≤, ≥)를 사용하여 조건의 양쪽에 기술 가능

city 테이블 상위 10개 조회

sql


SELECT * FROM city LIMIT 10;

country 테이블 상위 10개 조회

sql


SELECT * FROM country LIMIT 10;

city 테이블에서 한국(South Korea)에 해당되는 모든 도시 조회

sql


SELECT city_id, city
FROM city
WHERE country_id = (SELECT country_id FROM country WHERE country = 'South Korea');

country 테이블에서 인도(’India’)가 아닌 국가의 모든 열 조회

sql


SELECT * FROM country WHERE country <> 'India';

→ 108개의 행 조회

city 테이블에서 인도를 제외한 모든 국가(country)의 city_id, city를 조회

sql


SELECT city_id, city
FROM city
WHERE country_id =
(SELECT country_id FROM country WHERE country <> 'India');

→ country_id(표현식)과 표현식의 집합(1,2,3,4,5)이 서로 비교가 될 수 없기 때문에 에러 발생 Subquery returns more than 1 row → 동등 조건(=)에서 서브쿼리를 사용하였을 때 서브쿼리가 둘 이상의 행을 반환하면 오류가 발생함

하나의 열에 다중 행을 가진 결과셋을 반환하는 서브쿼리

위의 예시처럼 서브쿼리가 2개 이상의 행을 반환할 경우, 동등조건을 사용할 수 없음

하나의 값(= 기호 기준 좌측 customer_id)을 값의 집합(= 기호 기준 우측 서브 쿼리의 결과셋)과 동등하게(=) 비교할 수는 없지만 좌측 값에 해당하는 하나의 값이 특정 값 집합 내에 속하는지 여부(IN)는 사용할 수 있음

특정 집합에 속하는지 확인 가능한 IN 연산자

sql


SELECT country_id
FROM country
WHERE country IN ('Canada', 'Mexico');

City 테이블에서 country가 canada이거나 mexico에 속하는 모든 city_id, city를 조회

sql


SELECT city_id, city
FROM city
WHERE country_id IN
  (SELECT country_id 
	 FROM country
	 WHERE country IN ('Canada', 'Mexico'));

City 테이블에서 country가 canada이거나 mexico에 속하지 않는 모든 city_id, city를 조회

sql


SELECT city_id, city
FROM city
WHERE country_id NOT IN
  (SELECT country_id 
	 FROM country
	 WHERE country IN ('Canada', 'Mexico'));

ALL 연산자 All연산자를 사용하면 하나의 값과 하나의 집합에 있는 모든 값과 비교 가능

Payment 테이블에서 영화를 무료로 대여한 적이 있는 고객의 id 조회

sql


SELECT customer_id FROM payment WHERE amount = 0;

영화를 무료로 대여한 적이 없는 모든 고객의 fist_name, last_name 조회

sql


SELECT first_name, last_name
FROM customer
WHERE customer_id <> ALL
	(SELECT customer_id -- 영화를 무료(amount = 0)로 대여한 고객
	 FROM payment
	 WHERE amount = 0);

→ customer_id <> ALL은 서브쿼리에서 반환된 집합에 해당되는 id가 없는 모든 고객의 이름을 반환

ALL대신 NOT IN 연산자를 활용한 방식(가독성 up)

sql


SELECT first_name, last_name
FROM customer
WHERE customer_id NOT IN
	(SELECT customer_id
	 FROM payment
	 WHERE amount = 0)

이 외에도 다수의 비교값(집합 내 값) 중 하나라도 일치하는 값이 있을 경우 true를 반환하는 ANY 키워드도 있음

2. 둘 이상의 열을 반환하는 다중 열 서브쿼리

지금까지의 예시는 하나의 열과 하나 이상의 행을 반환하였음 상황에 따라 둘 이상의 열을 반환하는 다중 열 서브쿼리가 필요할 수도 있음

Film_actor 테이블 상위 5개 조회

sql


SELECT * FROM film_actor LIMIT 5;

성이 Monroe인 모든 배우 조회

sql


SELECT actor_id
FROM actor
WHERE last_name = 'MONROE';

Film 테이블에서 영화의 등급(rating)이 ‘PG’인 모든 film_id 조회

sql


SELECT film_id 
FROM film
WHERE rating = 'PG';

Film_actor 테이블에서 성이 Monroe인 배우가 출연한 영화 중 등급이 PG인 모든 actor_id, film_id 조회

sql


SELECT fa.actor_id, fa.film_id
FROM film_actor fa
WHERE fa.actor_id IN
	(SELECT actor_id FROM actor WHERE last_name = 'MONROE')
	AND fa.film_id IN
	(SELECT film_id FROM film WHERE rating = 'PG');