Subqueries - 서브쿼리

hyperskill - Subqueries 원문

일반적으로, 데이터를 작업 할 때, 첫 번째 쿼리의 실행 결과를 두 번째 쿼리에 사용 할 필요가 있습니다.

예를 들어, 한 컬럼의 값이 다른 컬럼의 최대 값과 동일한 튜플들을 선택하고자 합니다.

우리는 이것을 간단한 서브쿼리로 수행 할 수 있습니다. :

--> 최대 값을 선택하고, 메인 쿼리는 이 값과 동일한 튜플들을 선택합니다.

감사하게도, SQL은 결합 쿼리들을 위한 강력한 도구를 가지고 있습니다. = subqueries.

한 번 이를 살펴 봅시다!

subquery : (또한 inner 혹은 nested query 로 불립니다.)

SQL query : (main query 혹은 outer query 로 불립니다.)

서브쿼리 는 또다른 SQL 쿼리 입니다.

서브쿼리들은 SELECT, UPDATE, INSERT, and DELETE 문법 내부에서 중첩됩니다.

Subqueries nested in SELECT statement

- SELECT 문법에서 중첩 된 서브쿼리

---

SELECT 문법에 중첩 된 서브쿼리는 가장 평범한 유형의 서브쿼리입니다.

서브쿼리는 SELECT 절에서 발생할 수 있고, FROM, WHERE 절 에서도 발생 할 수 있는데,

SELECT 문으로 사용합니다.

밑의 예제를 따라 가 봅시다.

registered_users 테이블은 두 개의 컬럼을 가집니다 : username, sign_up_date:

username	sign_up_date
timbrown	2012-12-04
awesometomas	2014-11-06
darlingKate	2012-12-04
frMartin	2014-07-03

우리는 첫 번째로 등록된 유저들을 선택하기 위해 WHERE 절에 중첩된 서브쿼리를 사용 할 겁니다. :

SELECT
    username,
    sign_up_date
FROM
    registered_users
WHERE
    sign_up_date = (
        SELECT 
            MIN(sign_up_date)
        FROM
            registered_users
    );

쿼리의 결과는 밑을 따릅니다 :

username	sign_up_date
timbrown	2012-12-04
darlingKate	2012-12-04

위 예제의 서브쿼리는 single-row-subquery 입니다. (단일 줄 서브쿼리)

이 경우, 간단한 SQL 비교 연산자를 사용하기에 안전한데, (=,>,<=, 등등...)

우리의 쿼리가 오직 단 하나의 컬럼에 단 하나의 줄을 반환 하는 것이 확실하기 때문입니다.

만약 우리가 비교 연산자와 함께 서브쿼리를 사용하고, 서브쿼리가 한 줄 초과을 반환한다면, 에러가 납니다.

위의 에러는 당신이 여러 줄을 반환하는 multiple-row subquery(다중 줄 서브쿼리) 를 사용하지 못한다는 의미가 아닙니다.

그저 IN, ANY와 같은 다중 줄 비교 연산자를 사용하라는 겁니다.

결과 집합에 새로운 컬럼을 추가하기 위해서

SELECT 문법 내부에 컬럼 중 하나를 선택하는 서브쿼리를 사용 할 수 있습니다.

테이블 registered_users 로 부터 모든 데이터를 선택하고,

밑에 주어진 테이블 users_info 로부터 정보를 조금 더 추가 해 봅시다.

username	name	birth_date
awesometomas	Tomas Jones	1995-10-07
timbrown	Tim Brown	2000-11-04
frMartin	Martin Brown	2002-12-04
darlingKate	Kate Brown	2005-03-03

registered_users 테이블에서 모든 정보를 선택하고,

users_info 테이블에서 실제 유저 이름에 대한 알맞는 정보를 추가 합니다. :

SELECT *, (
    SELECT
        name
    FROM
        users_info
    WHERE
        username = registered_users.username
    ) AS name
FROM
    registered_users;

결과는 밑과 같습니다 :

username	sign_up_date	name
timbrown	2012-12-04	Tim Brown
awesometomas	2014-11-06	Tomas Jones
darlingKate	2012-12-04	Kate Brown
frMartin	2014-07-03	Martin Brown

우리가 이미 말했듯이, 당신은 FROM 절에서 SELECT 문법의 서브쿼리를 사용할 수 있습니다.

이 경우에서, 파생된 테이블 (서브쿼리로 우리가 얻은 테이블) 은 별칭되었습니다. : AS name

서브쿼리들은 상호 연관 될 수 있습니다.

상호 연관 된 서브쿼리들은 바깥 쿼리(메인 쿼리)의 값을 이용할 수 있습니다.

상호 연관된 서브쿼리의 예제를 살펴 보겠습니다.

여기 테이블 new_orders에 이러한 컬럼들을 가집니다 :

• id INT
• product VARCHAR(40)
• product_category VARCHAR(40)
• quantity INT
• unit_price INT

id	product	product_category	quantity	unit_price
1234	table	furniture	10	15
3434	chair	furniture	15	20
4546	bed	furniture	12	10
5467	candle	decor	45	40
3244	sticker	decor	40	14
3456	frame	decor	34	12

우리는 제품 카테고링츼 평균 가격보다 더 작은 unit_pricd를 가진

모든 제품들을 선택하기 위해 상호 연관된 서브쿼리를 사용 할 수 있습니다. :

SELECT 
    id,
    producct
FROM
    new_orders AS newor
WHERE
    unit_price < (
        SELECT
            AVG(unit_price)
        FROM
            new_orders
        WHERE
            product_category = newor.product_category
        );

쿼리의 결과가 계산되고 있는 동안, 상호 연관된 쿼리는 테이블의 각 줄 마다 실행되므로,

서브쿼리를 가진 계산 쿼리는 거대한 테이블에 대해서는 많은 시간이 걸릴 수 있습니다.

한 줄 마다, WHERE 절 내부의 서브쿼리가 한 번 씩 실행되므로, 연산이 느릴 수도 있다는 의미이다.

Subqueries nested in the UPDATE statement

- UPDATE 문법 내부의 중첩된 서브쿼리

---

서브쿼리는 UPDATE 문법에서도 사용 될 수 있습니다.

students 테이블을 업데이트 해 봅시다. 이 테이블은 밑의 컬럼을 가집니다.

• name VARCHAR(40)
• scholarship INT
• exams_passed BOOLEAN

name	scholarship	exams_passed
Tom Jones	200	FALSE
Tamara Fetch	400	FALSE
Anthony Pots	300	FALSE

SELECT 문법에 서브쿼리를 사용했던 것과 마찬가지로,

UPDATE 문법에도 WHERE 절 내부에 중첩된 서브쿼리를 사용 할 수 있습니다.

우리는 서브쿼리를 지닌 이러한 유형의 쿼리를 exams_passed를 TRUE로 설정하기 위해 사용 할 건데,

exam_results 테이블 내부의 두 시험이 둘 다 18점보다 크거나 같다면 TRUE로 설정합니다.

exam_results 테이블은 밑의 컬럼들을 가지고 있습니다.

• name VARCHAR(40)
• math_exam_mark INT
• english_exam_mark INT

name	math_exam_mark	english_exam_mark
Tom Jones	22	23
Tamara Fetch	18	15
Anthony Pots	18	18

students 테이블을 업데이트 하기 위해 밑의 쿼리를 사용합니다 :

UPDATE
    students
SET
    exams_passed = TRUE
WHERE
    name IN (
        SELECT
            name
        FROM
            exam_results
        WHERE
            math_exam_mark >= 18
            AND english_exam_mark >= 18
        );

name IN ( subqueries )

해당 쿼리는 name 값이 subqueries로 나오는 결과값들 중 하나에 해당된다면

name IN ( ... 의미는 name 은 subquery의 결과 중 하나에 속한다 라는 의미가 됩니다.

위 쿼리 실행 후, 테이블 students는 밑과 같습니다 :

name	scholarship	exams_passed
Tom Jones	200	TRUE
Tamara Fetch	400	FALSE
Anthony Pots	300	TRUE

우리는 서브쿼리를 가진 UPDATE를 값을 조정하기 위해 사용 할 수도 있습니다.

우리는 밑의 쿼리를 사용할 건데,

시험들을 통과하지 못한 모든 학생들에 대해 최소한의 장학금(scholarship) 으로 설정하기 위해 사용합니다.

scholarships 테이블에 장학금 amount가 저장되어 있다고 가정하겠습니다.

UPDATE
    students
SET
    scholarship = (
        SELECT
            MIN(amoun)
        FROM
            scholarships
    )
WHERE
    exams_passed = FALSE;

이제 Tamara Fetch 학생의 장학금은 200과 동일합니다 :

name	scholarship	exams_passed
Tom Jones	200	TRUE
Tamara Fetch	200	FALSE
Anthony Pots	300	TRUE

Subqueries nested in the INSERT statement

- INSERT 문법 내부의 중첩된 서브쿼리

---

INSERT 문법에서,

우리는 서브쿼리에서 반환된 데이터를 다른 테이블에 삽입하기 위해 서브쿼리를 사용 할 수 있습니다.

밑의 예제는 employees 테이블로 작업하게 되며, 이러한 컬럼을 가집니다 :

• name VARCHAR(20)
• salary INT
• department_id INT

name	salary	department_id
Ann Reed	4000	1

department (부서) 에 대한 모든 데이터는 departments 테이블에 저장되어 있습니다.

• id INT
• department VARCHAR(20)

id	department
1	HR
2	IT
3	PR

employees 테이블에 Tomas Hedwig 직원에 대한 정보를 추가 해 봅시다.

이 직원은 PR 부서에서 일하며, 연봉은 Ann Reed의 연봉과 동일합니다.

연봉과 부서 ID를 얻기 위해서 우리는 밑에 보여진 서브쿼리들을 사용 할 겁니다 :

INSERT INTO employees
VALUES (
    'Tomas Hedwig',
    (SELECT salary FROM employees WHERE name = 'Ann Reed'),
    (SELECT id FROM departments WHERE department = 'PR')
);

쿼리 실행 이후, employees 테이블은 두 개의 줄을 가지게 됩니다 :

name	salary	department_id
Ann Reed	4000	1
Tomas Hedwig	4000	3

Subqueries nested in the DELETE statement

- DELETE 문법 내부의 중첩된 서브쿼리

---

우리는 DELETE 문법에서 서브쿼리들을 사용할 수도 있는데,

WHERE 절 내부 조건의 일부로 사용 할 수 있습니다.

두 개의 테이블을 가지고 있다고 가정하겠습니다.

1. 테이블 orders

• order_id INT
• customer_id INT
• product VARCHAR(20)
• city VARCHAR(20)

order_id	customer_id	product	city
1	1	shampoo	London
2	1	hair mask	London
3	2	hair mask	London

2. 테이블 customers

• customer_id INT
• name VARCHAR(40)

customer_id	name
1	Ann Smith
2	John Doe
3	Sam Brown

우리는 orders 테이블에서 Ann Smith 가 만든 모든 주문들을 삭제 할 겁니다.

DELETE FROM orders
WHERE 
    customer_id = (SELECT customer_id FROM customers WHERE name = 'Ann Smith')
;

이 쿼리 실행 후, orders 테이블을 밑과 같이 생겼습니다 :

order_id	customer_id	product	city
3	2	hair mask	London

UPDATE 때와 마찬가지로, DELETE 문법에서도 쿼리와 서브쿼리에서 동일한 테이블을 사용 할 수 없습니다.

Conclusion - 결론

---

이번 주제에서는, 서로다른 서브쿼리의 유형에 대해 배워 봤습니다.

이제 당신은 쿼리의 서로 다른 부분에서 서브쿼리를 사용 할 수 있으며,

서로 다른 유형의 구문에서도 서브쿼리를 사용 할 수 있습니다.

이제 연습하러 가 봅시다!