SQL

[SQL] 중첩 질의, 상관 중첩 질의

홍팡 2022. 11. 18. 23:11

어느덧 벌써 올해가 며칠 안남은게 느껴진다.

올해 초부터 데이터분석 공부를 시작해서 부랴부랴 알아보다 보니 SQL이란 것이 있더라.

 

데이터 베이스를 생성하거나 조회, 수정 등 할 수 있는 SQL 이란 것이 있다고 해서

4-2 학기에 수강신청을 통해 공부를 시작하게 되었다.

 

이미 정보처리기사나 SQLD 시험 볼 때 접해봤던 것들이라

배우는 내용이 크게 어렵지는 않았지만 

막연한 개념으로만 알고 시험을 쳤었기에, 수업 중에 실습을 하는 것에 있어서 버벅거림이 있기도 하고

SQL문을 복습하고자 하는 느낌으로 글을 작성하기로 하였다.

 

계획대로면, DB의 정의니 스키마니 처음부터 시작해야 하는 것이 맞지만

당장 그럴 시간도 없고 지금 공부하고 있는 부분부터 올리려고 한다.

 

 

 

 

 

1. 중첩 질의

중첩질의(nested subquery)는 where 절에서 사용되는 부속질의를 말한다.

다른 질의의 where 절 내에서 (select-from-where) 블록을 완료한다.

기본 쿼리문에서, where 절에 다시 select문으로 시작하는 쿼리를 넣어서 질의를 구성하는 것이 중첩질의이다.

 

 

 

  • IN 비교 연산자

- 값 v를 값들의 집합 (또는 다중집합) V와 비교함.

- v가 V내의 원소 중 하나이면 TRUE로 평가된다.

 

IN 연산자의 경우 where 절 내에서 특정값 여러개를 선택하는 연산자로 괄호 내의 값 중 일치하는 것이 있으면 TRUE를 반환한다.

 

 

 

SELECT * FROM 테이블명 WHERE 컬럼명 IN ();

위와 같은 SQL 문에서 

괄호 () 안에는 어떠한 값들이 들어갈 수 있는데

컬럼명과 괄호 안의 값이 일치하는 튜플들을 내보낸다.

SELECT * FROM FRUITS WHERE name IN ('Apple', 'Banana', 'Mango');

name = 'Apple' or name = 'Banana' or name = 'Mango'

FRUITS 테이블에서 name이 Apple, Banana, Mango 인 튜플을 반환한다.

 

 

IN 안에는 값 뿐만 아니라 다른 SQL문을 중첩하여 사용할 수 있다.

SELECT * FROM FRUITS WHERE name IN (SELECT name FROM Mart WHERE price <= 10000);

FRUITS 테이블과 Mart 테이블이 있을 때,

Mart 테이블에서 가격(price)이 10000 이하인 상품의 name 을 반환하고

FRUITS 테이블에서 name이 일치하는 튜플을 반환한다.

 

 

 

비교할 때 값을 튜플(짝)을 사용할 수 있다.

SELECT DISTINCT Essn FROM WORKS_ON WHERE (Pno,Hours) IN (SELECT Pno,Hours FROM WORKS_ON WHERE Essn='12345679');

※ DISTINCT 가 들어가면 중복인 튜플을 삭제해서 조회해준다.

WORKS_ON 이라는 테이블에서 Pno와 Hours 를 조회하는데, Essn이 123456789 인 Pno와 Hours 만을 조회한다는 뜻.

 

 

 

 

 

  • ANY (= SOME) 연산자

- 값 v가 집합 V의 일부 값과 같으면 TRUE를 리턴함. 이는 IN과 동일한 연산자임

- 그러나 ANY는 >, >=, <, <=, <> 등과 결합할 수 있다. 즉, 비교 연산자를 사용한다는 점

 

SELECT * FROM FRUITS WHERE name = ANY(SQL문);

이는 위에서 서술한 IN과 동일한 연산을 한다.

 

 

※ 여러가지 실습을 해보았는데 ANY는 IN과 다르게 괄호 안에 단일 값이 아니라 

서브쿼리를 통한 값이 나와야 하는 것 같다.

employee 테이블

SELECT fname, lname FROM employee WHERE sex = ANY(SELECT sex FROM employee WHERE salary = 43000);
SELECT fname, lname FROM employee WHERE sex = ANY('F');

 

ANY를 통한 연산에서 

salary = 43000인 사람의 성별은 F이고 첫 번째 질의를 통해서

이와 같은 값이 나오게 된다.

 

그러나 두 번째 질의를 하면 

이와 같은 오류가 발생하게 된다.

ANY 연산을 사용할 때는 주의하도록 하자.

(실습용으로 Maria DB를 사용 중인데 Maria DB만의 문제일 수도 있다.)

 

 

SELECT fname, lname FROM employee WHERE sex > ANY(SELECT sex FROM employee WHERE salary = 43000);

이 질의에서 위와 다른 점은 ANY 앞에 비교연산자 '>'가 들어갔다는 점인데

저 질의를 하고 나면 성별이 남성인 사람의 fname과 lname이 나오게된다.

그러나 반대로 '<' 연산자를 하면 아무런 값이 나오지 않는다.

 

(문자열과의 대소 비교이기 때문에, 어떠한 값이 나오지 않을 줄 알았는데

자세히 찾아봐야 알겠지만 내부적으로 어떠한 연산을 한다거나, 아직 SQL 작동 원리를 모르고 있어 내가 착각하고 있을 수 있겠다는 생각이 들었다.)

 

 

  • >  ANY : 최소값 보다 큰 값
  • >= ANY : 최소값 보다 크거나 같은 값
  • < ANY : 최대값 보다 작은 값
  • <= ANY : 최대값 보다 크거나 작은 값
  • <> ANY : NOT IN과 같은 효과
  • = ANY : IN과 같은 효과 

 

 

 

 

  • ALL 연산자

- 값이 중첩 질의를 통해 나온 모든 값들에 대해 만족해야 한다.

 

위에서 IN과 ANY 연산은 서브쿼리 절 안의 값들 중 하나라도 일치하면 TRUE를 반환하였다.

이는 논리연산자 'OR' 와 같은 역할을 한다.

그러나 ALL 연산의 경우에는 서브쿼리 절 안의 값들 모두와 일치할 때 TRUE를 반환한다.

즉, 논리연산자 'AND'와 같은 역할을 한다.

 

 

select * from emp where sal = ALL(950, 3000, 1250);

(emp = 950) AND (emp = 3000) AND (emp = 1250) 

이라는 결과이기 때문에 결과가 하나도 나오지 않는다.

 

SELECT * FROM emp WHERE sal > ALL(950, 3000, 1250);

 

(emp > 950) AND (emp > 3000) AND (emp > 1250) 

이라는 결과이기 때문에 sal 이 3000 이상인 튜플만 반환된다.

 

 

예시는 이러한 형태로 가져왔지만

ANY와 동일하게 저런 형태의 단일 값으로 SQL문을 돌리게 되면 

Maria DB 에서는 돌아가지 않는다.  :<

 

 

  • > ALL : 최대값 보다 큰 값
  • >= ALL : 최대값 보다 크거나 같은 값
  • < ALL : 최소값 보다 작은 값
  • <= ALL : 최소값 보다 작거나 같은 값

 

 

 

 

2. 상관 중첩 질의

 

상관 중첩 질의는, 일반 중첩 질의와 다르게 

외부 질의에서 FROM절에 사용한 약어를

내부 질의에서도 사용하는 케이스이다.

 

SELECT E.fname, E.lname 
FROM employee as E
WHERE dno IN (SELECT dnumber FROM department as D WHERE E.ssn = D.mgr_ssn);

이런 식으로 외부 질의에서 사용된 employee(E)를 

WHERE 절 안에서 (E.ssn = D.mgr_ssn) 식으로 재사용되는 것을 상관 중첩 질의라고 한다.

 

 

 

 

  • EXISTS

- EXISTS는 상관 중첩 질의의 결과가 비어있는 지를 TRUE와 FALSE로 반환하는 Boolean 함수이다.

 

기본적으로 EXISTS는 IN과 유사한 개념이지만, 적용되는 범위가 다르다.

IN의 괄호() 사이에는 특정 값이나, 서브쿼리가 올 수 있는 반면에

EXISTS의 괄호() 사이에는 서브쿼리만 올 수 있다.

 

IN과 EXISTS는 동일한 결과를 보여줌에도 불구하고 EXISTS가 더 좋은 성능을 보여주기 때문에

단순히 특정 컬럼의 값을 이용할 때는 IN을 이용하고 서브쿼리를 이용할 때는 EXISTS를 이용하는 것이 성능이 더 좋다.

 

 

customer table

number name grade
001 hong A
002 kim B
003 lee B+
004 park A
005 choi C+

order table

id number sub_id
12345 003 54321
12346 004 64321
12347 002 74321
12348 003 84321
12349 004 94321

 

- EXISTS

SELECT * FROM customer WHERE EXISTS (SELECT * FROM order WHERE order.number = customer.number);

 

- IN

SELECT * FROM customer WHERE number IN (SELECT number FROM order);

 

EXISTS와 IN의 결과는 동일하지만

EXISTS는 서브쿼리 절 안의 결과의 TRUE or FALSE 만을 반환하기 때문에 실행 속도가 빠른 반면

IN은 각각 튜플과 서브쿼리 절 안의 연산 결과를 일치하는 지 확인하기 때문에 실행 속도가 EXISTS 보다 느리다고 한다.