[SQL] 17. SQL Subquery

📖 Subquery

🌟 Subquery

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

📌 Subquery 사용 시 주의사항

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

🌟 Subquery 종류

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

---

📖 Scalar Subquery

🌟 Select절에서 사용하는 서브쿼리

• 결과는 하나의 Column이어야 한다.

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

💭 Scalar Subquery 예제

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

mysql> select case_number , (select avg(case_number) 
							 from crime_status 
                             where crime_type = '강도' 
                             and status_type = '검거') avg   
    -> from crime_status 
    -> where police_station like '은평' 
    -> and crime_type = '강도' and status_type ='검거';
    
    
+-------------+--------+
| case_number | avg    |
+-------------+--------+
|           1 | 4.1935 |
+-------------+--------+
1 row in set (0.02 sec)

---

📖 Inline View

🌟 FROM 절에 사용하는 서브쿼리

• 메인 쿼리에서는 인라인뷰에서 조회한 Column만 사용가능

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

💭 Inline View 예제

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

mysql> 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
    -> limit 5;
+----------------+------------+-------------+
| police_station | crime_type | case_number |
+----------------+------------+-------------+
| 중부           | 폭력       |         997 |
| 종로           | 폭력       |         964 |
| 남대문         | 절도       |         699 |
| 서대문         | 폭력       |        1292 |
| 혜화           | 폭력       |         747 |
+----------------+------------+-------------+

---

📖 Nested Subquery

🌟 Where 절에서 사용하는 서브쿼리

• Single Row : 하나의 열을 검색
• Multiple Row : 하나 이상의 열을 검색
• Multiple Column : 하나 이상의 행을 검색

🌟 Single Row Subquery

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

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

💭 Single Row Subquery 예제

• 괄호 없이 ( Error )

mysql> select name 
	-> from celeb 
	-> where name = select host from snl_show;

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'select host from snl_show' at line 1

• 한 개 이상의 결과 ( Error )

mysql> select name 
	-> from celeb 
    -> where name = (select host from snl_show); 

ERROR 1242 (21000): Subquery returns more than 1 row

• Single Row 예제

mysql> select name 
	-> from celeb 
    -> where name = (select host from snl_show where id = 1); 

+-----------+
| name      |
+-----------+
| 강동원    |
+-----------+
1 row in set (0.01 sec)

---

🌟 Multiple Row

🌟 IN

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

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

🌟 EXISTS

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

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

🌟 ANY

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

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

🌟 ALL

• 서브쿼리 결과를 모두 만족하면 반환
• 비교연산자 사용

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

💭 Multiple Row 예제

💭 IN 예제

• SNL에 출연한 배우를 조회

mysql> select host                 
    -> from snl_show
    -> where host in (select name
    ->                from celeb
    ->                where job_title like '%배우%');

+-----------+
| host      |
+-----------+
| 강동원    |
| 차승원    |
+-----------+
2 rows in set (0.00 sec)

💭 EXISTS 예제

• 범죄 검거 혹은 발생 건수가 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);
    
+--------------------------+
| name                     |
+--------------------------+
| 서울강남경찰서           |
| 서울강서경찰서           |
| 서울관악경찰서           |
| 서울구로경찰서           |
| 서울노원경찰서           |
| 서울송파경찰서           |
| 서울영등포경찰서         |
| 서울중랑경찰서           |
+--------------------------+
8 rows in set (0.02 sec)

💭 ANY 예제

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

mysql> select name  
    -> from celeb 
    -> where name = any (select host from snl_show);
    
+-----------+
| name      |
+-----------+
| 강동원    |
| 유재석    |
| 차승원    |
| 이수현    |
+-----------+
4 rows in set (0.00 sec)

💭 ALL 예제

• SNL에 출연한 적이 있고, ID가 1인 연예인 이름 조회 (table : snl_show )

mysql> select name 
    -> from celeb 
    -> where name = any (select host from snl_show where id = 1 ); 
    
+-----------+
| name      |
+-----------+
| 강동원    |
+-----------+
1 row in set (0.00 sec)

---

🌟 Multiple Column Subquery : 연관서브쿼리

• 서브쿼리 내에 메인쿼리 칼럼이 같이 사용

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

💭 Multiple Column Subquery 예제

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

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엔터   |
+-----------+------+----------+
2 rows in set (0.01 sec)