서브쿼리 Sub Query - 상관쿼리와 비상관쿼리

서브쿼리에서 어려운 점은 구조가 아니고, 쿼리의 어느 부분이 서브 쿼리인지 또는 서브 쿼리를 사용할 필요가 있는지를 알아내는 것입니다. 

 

Q. 내 리스트에 있는 사람들 중에 누가 연봉이 가장 많을까? 

- 내 리스트의 누가? SELECT first_name, last_name FROM my_contact_list
- 내 리스트의 연봉? SELECT salery FROM job_current;
- 가장 많은 연봉: SELECT MAX(salary) FROM job_current;  

my_contact_list mc 와 job_current jc 에서 mc.first_name, mc.last_name, jc.salery 를 가져옵니다.
그런데 조건! jc.salery 가 가장 높은 연봉인 사람만! 가장 높은 연봉은 값이 딱 하나입니다. 

SELECT mc.first_name, mc.last_name, jc.salery 
FROM my_contact mc NATURAL JOIN job_current jc 
WHERE jc.salery = ( SELECT MAX(salary) FROM job_current) ;

만약 서브쿼리가 외부 쿼리의 어떤 것도 참조하지 않고 단독으로 사용되면 비상관쿼리 입니다! 내부 쿼리가 우선 실행되고, 그리고 나서 결과가 외부쿼리의 WHERE 조건으로 이용됩니다. 하지만 내부 쿼리는 외부 쿼리의 값과는 아무 상관이 없습니다. 단독으로도 쿼리가 실행될 수 있습니다. 

 

 

Q. 내 리스트에 있는 모든 사람들이 어느 주에 살까?

- 내리스트의 모든사람: SELECT first_name, last_name, zip_code FROM my_contact
- 이들은 어느주에? : SELECT state FROM zip_code;

my_contact 에서 fist_name, last_name, zip_code 그리고 zip_code를 활용한 state 를 가져옵니다.
my_contact 의 값, mc.zip_code 로 state를 찾아야 하는 것이니, 상관쿼리입니다. 상관쿼리는 외붜쿼리부터 실행됩니다.  
zip_code 테이블에서 mc.zip_code 값과 동일한 zip_code 를 가지는 건의 state 로 가져옵니다. 

SELECT mc.first_name, mc.last_name, zip_code,
( SELECT state FROM zip_code WHERE zip_code = mc.zip_code) AS state
FROM my_contacts mc;

상관서브쿼리는 내부 쿼리의 값이 결정되는데 외부 쿼리에 의존합니다.  외부 쿼리의 zip_code 값이 있어야 내부 쿼리가 수행될 수 있습니다. 

 

Q.  내 리스트의 사람들 중 현재 직업이 없는 사람에게 이메일을 보내려면?

- 내 리스트 사람과 이메일: SELECT first_name, last_name, email FROM my_contacts;
- 직업이 있는 사람: SELECT *  FROM job_current;

SELECT mc.first_name, mc.last_name, mc.email
FROM my_contacts mc 
WHERE NOT EXISTS ( SELECT * FROM job_current jc WHERE mc.contact_id = jc.contact_id );

위 쿼리도 상관서브쿼리이다. my_contact mc 테이블의 mc_contact_id 마다 job_current 테이블에 그 값이 있는지 없는지를 확인하기 때문.
WHERE 절에는 = 과 IN 뿐만 아니라 다양한 비교연산자들이 들어올 수 있다. LIKE, BETWEEN, EXISTS, NOT EXISTS 도 들어올 수 있다.

 

Q. 내 리스트의 사람들 중 관심사가 하나 이상 있는 사람에게 이메일을 보내려면?

- 내 리스트 사람과 이메일: SELECT first_name, last_name, email FROM my_contacts;
- 관심사가 있는 사람: SELECT *  FROM contact_interest;

SELECT mc.first_name, mc.last_name, mc.email
FROM my_contacts mc
WHERE EXISTS ( SELECT * FROM contact_interest ci WHERE mc.contact_id = ci.contact_id) ;

SELECT mc.first_name, mc.last_name, mc.email 
FROM my_contacts mc
WHERE EXISTS ( SELECT * FROM contact_interest ci WHERE mc.contact_id = ci.contact_id) ;