[DataBase] 03. SQL(Structured Query Language)(3)

 

SQL(Structured Query Language)(3)

#Database/Concepts

(10/10) 굉장히 공들여서 작성한 글이여서 원래 동아리 스터디 용도로 일부 공개 했었는데, 더 많은 분들이 봤으면 하는 마음에 오픈!

게시글 길이 제한으로 글을 분리해서 업로드하는 점 양해 바랍니다

중첩 서브쿼리 (Nested Subqueries)

앞 챕터에서 관계 연산의 조합을 다루면서 “관계 대수 연산의 결과는 relation이다. 따라서 관계 대수 연산의 결과인 Relation을 다시 관계 대수 연산Relation으로 사용할 수 있다” 라고 했던 걸 기억할 것이다.
이를 SQL에서도 제공하고 있다. select-from-where 쿼리의 결과로 얻은 릴레이션을 다시 쿼리에 사용할 수 있다. 중첩 서브쿼리는 SQL 쿼리 내에서 다른 쿼리를 포함하는 것을 말한다. (서브쿼리를 내부쿼리라고도 한다.)

• SQL은 서브쿼리의 중첩 메커니즘을 제공합니다.
  • 서브쿼리는 더 동적이고 데이터 중심적인 쿼리를 작성할 수 있게 합니다.
  • 서브쿼리는 다른 쿼리 내에 중첩된 select-from-where 표현식입니다.
  • 다음 SQL 쿼리에서 중첩이 가능합니다.

    select A1, A2, ..., An
    from r1, r2, ..., rm
    where P
    

• From 절: r_i는 자리에 서브쿼리를 사용할 수 있다.
  • 즉, 서브쿼리가 하나의 테이블처럼 동작하여 그 결과를 사용해 외부 쿼리를 실행한다.
  • 쿼리의 결과가 relation이기 때문

  SELECT A1, A2, ...
  FROM (SELECT ... FROM ... WHERE ...) AS subquery_name
  WHERE ...;
  

• Where 절 : 서브쿼리의 결과를 사용해 조건을 정의할 수 있다. 서브쿼리의 결과는 일반적으로 값 또는 집합을 반환하며, 외부 쿼리는 이를 조건으로 사용하여 필터링을 수행한다.
  • Where 절의 P는 B (subquery) 형태의 표현식으로 대체될 수 있습니다.
  • 여기서 B는 attribute이고 은 연산입니다.

  SELECT A1, A2, ...
  FROM table_name
  WHERE column_name = (SELECT ... FROM ... WHERE ...);
  

• Select 절: 서브쿼리를 통해 계산된 값을 특정 열로 선택할 수 있다. 이 경우 서브쿼리는 단일 값을 반환해야 한다.
  • A_i는 단일 값을 생성하는 서브쿼리로 대체될 수 있습니다.

  SELECT (SELECT ... FROM ... WHERE ...) AS column_name
  FROM table_name;
  

중첩 서브쿼리의 예 (Example of Subqueries)

예시: 미국에 위치한 사무실에서 근무하는 직원을 찾기 - Where절

SELECT lastname, firstname
FROM employees
WHERE officeCode IN (
  SELECT officeCode
  FROM offices
  WHERE country = 'USA'
);

• 서브쿼리는 미국에 위치한 사무실의 모든 office code를 반환합니다.
• 외부 쿼리는 서브쿼리에 의해 반환된 office code에 해당하는 사무실에서 근무하는 직원의 성과 이름을 선택합니다.

위 예시의 Where절에서 employess 테이블에 서브쿼리에서 얻는 officeCode 목록에 포함되어 있는 레코드만 선택하기 위해서 IN이라는 연산자를 사용하고 있다. 이에 대해 자세히 알아보자.

Set Membership

Set Membership는 어떤 값이 특정 집합에 속하는지 여부를 확인하는 작업을 의미한다. 위에서 다룬 것처럼 서브 쿼리로 얻은 Relation에 해당 튜플이 포함되어 있는지 확인하는 것도 이에 해당한다.

IN과 NOT IN 연산자를 사용한다.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT statement);

Set Membership 예시
2017년 가을학기와 2018년 봄 학기에 열린 과목 찾기

2017년 가을학기에 열렸지만 2018년 봄 학기에 열리지 않은 과목 찾기

이름이 “Mozart”나 “Einstein”이 아닌 교수님의 이름 찾기

ID가 10101인 교수님이 가르치는 과목을 수강하는 학생 수 구하기

참고 : 위의 쿼리는 훨씬 더 간단한 방식으로 작성할 수 있습니다. 위의 공식은 단순히 SQL 기능을 설명하기 위한 것입니다.

Set Comparison

Set Comparision은 두 집합 간의 관계를 비교하는 작업이다. SOME 또는 ALL 절을 사용한다. 이를 통해 집합 포함성, 차집합, 교집합, 대칭 차집합 등의 연산을 할 수 있다.

• 값과 목록 또는 쿼리의 결과를 비교하여 참인지 평가합니다.
  • some 절
  • all 절

처음 보면 감이 잘 안올 것이다. 아래 예제를 통해 감을 익혀보자

some clause

하나라도 만족하면 TRUE를 반환한다.

• some은 목록 또는 쿼리의 결과에서 각 값을 비교하여 내재된 쿼리 결과가 최소 한 행을 포함하면 참으로 평가합니다.
  • some은 서브쿼리에서 최소 한 행과 일치해야 하며 비교 연산자가 앞에 와야 합니다.
  • some을 사용하여 greater than (>)를 사용하는 경우, 적어도 하나의 값보다 큽니다. 자세한 건 아래 예시 그림을 확인해보자

예시: Set Comparison (some clause) 예제

예시: 소비 지역이 “UK”인 에이전트 리스트에 포함되는 에이전트를 선택

Agent Table

Customer Table

Result

• 서브 쿼리의 결과로 (A009, A006, A003)을 얻을텐데, agents 테이블의 agent_code가 A003, A006, A009 중 적어도 하나랑 일치하는 레코드. 즉 서브 쿼리의 결과에 포함되는 에이전트를 선택한다고 보면 된다. 이처럼 some 절을 통해서 집합 포함성을 체크할 수 있다.

예시: 생물학과의 일부(최소 한 명) 강사보다 급여가 높은 강사의 이름 찾기

• 만약 Set Comparision을 사용하지 않고 쿼리를 작성한다면?

• some clause를 사용한다면?

all clause

모두 조건을 만족해야 TRUE를 반환한다.

• ALL은 SELECT 문의 모든 레코드를 선택하는 데 사용됩니다.
  • ALL은 쿼리의 결과 목록이나 결과에 있는 모든 값과 값을 비교합니다.
  • ALL은 비교 연산자 앞에 와야 하며, 쿼리가 아무런 행도 반환하지 않으면 TRUE로 평가됩니다.
  • 예를 들어, ALL은 모든 값보다 크다는 의미로, 최대값보다 크다는 것을 의미합니다.

예시: ALL clause 예시

구문

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);

예시 : dispatch 테이블의 dis_amount 값이 orders 테이블에서 ord_amount 값이 2000 이상인 조건을 만족하는 ord_amount 값보다 큰 경우, dis_date, dis_amount, ord_amount 열을 선택

SELECT dis_date, dis_amount, ord_amount
FROM dispatch
WHERE dis_amount > ALL(SELECT ord_amount
  FROM orders
  WHERE ord_amount >= 2000);

예시 : Biology 부서의 모든 강사보다 급여가 높은 강사의 이름을 찾기

SELECT name
FROM instructor
WHERE salary > ALL (SELECT salary
FROM instructor
WHERE dept_name = 'Biology');

EXISTS Clause

서브쿼리의 결과가 존재하는지 확인하는 데 사용된다.
반환된 행이 있는지만 체크하고 있으면 True, 없으면 False를 반환한다.

• EXISTS는 SQL에서 중첩 서브쿼리의 결과가 비어 있는지(튜플이 없는지) 확인하는 데 사용됩니다.
  • EXISTS의 결과는 TRUE 또는 FALSE의 부울 값입니다.
  • EXISTS 구성은 인수 서브쿼리가 비어 있지 않으면 TRUE를 반환합니다.
• NOT EXISTS는 EXISTS와 반대로 작동한다

구문

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name(s)
FROM table_name
WHERE condition);

예시: 최소 하나의 주문을 한 고객의 이름

SELECT fname, lname
FROM Customers
WHERE EXISTS (SELECT *
FROM Orders
WHERE Customers.customer_id = Orders.c_id);

예시: 주문을 하지 않은 고객의 이름

SELECT lname, fname
FROM Customer
WHERE NOT EXISTS (SELECT *
FROM Orders
WHERE Customers.customer_id = Orders.c_id);

참고 | EXISTS VS IN 연산자

공통점 : 둘 다 WHERE 절에서 조건에 따라 필터링된 결과를 조회할 때 사용된다.

차이점 :

EXISTS는 조건에 해당하는 Row가 존재하는지만 체크한다. 즉 (서브쿼리의) SELECT 절까지 평가하지 않는다.

IN은 조건에 해당하는 Row의 Column을 비교한다. 즉 (서브쿼리의) SELECT 절에서 조회한 Column으로 값을 비교하므로 EXIST에 비해 성능이 떨어진다. (대량의 데이터를 다룰 때 비효율적이다)

실행 순서:

EXISTS : 메인 쿼리의 결과 값을 서브 쿼리에 대입하여 조건 비교 후 결과를 출력한다 (메인 쿼리의 각 행에 대해 EXISTS 쿼리가 실행됨).

IN : 서브 쿼리의 결과값을 메인 쿼리에 대입하여 조건 비교 후 결과를 출력한다.

NULL값 처리:

NOT EXISTS: NULL값에 대해 TRUE를 반환한다

NOT IN: NULL값에 대해 FALSE를 반환한다.