9.3 KiB
9.3 KiB
24강. 상관 서브쿼리
서브쿼리를 사용해 DELETE 명령과 SELECT 명령을 결합할 수 있었다. 스칼라 서브쿼리가 사용하기 쉬운 서브쿼리란 것도 알았다. 여기서는 서브쿼리의 일종인 상관 서브쿼리를 EXISTS 술어로 조합시켜서 서브쿼리를 사용하는 방법에 관해 알아본다.
EXISTS
EXISTS (SELECT 명령)
EXISTS술어를 사용하면 서브쿼리가 반환하는 결괏값이 있는지를 조사할 수 있다.- 특히
EXISTS를 사용하는 경우에는 서브쿼리가 반드시 스칼라 값을 반환할 필요는 없다. EXISTS는 단지 반환된 행이 있는지를 확인해보고 값이 있으면 참, 없으면 거짓을 반환하므로 어떤 패턴이라도 상관없다.
1. EXISTS
- 서브쿼리를 사용해 검색할 때
데이터가 존재하는지 아닌지판별하기 위해 조건을 지정할 수도 있다. - 이런 경우
EXISTS술어를 이용해 조사할 수 있다.
SELECT * FROM sample551;
| no | a |
|---|---|
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
SELECT * FROM sample552;
| no2 |
|---|
| 3 |
| 5 |
지금부터 sample552에 no 열의 값과 같은 행이 있다면 '있음'이라는 값으로, 행이 없으면 '없음'이라는 값으로 갱신하도록 하겠습니다. 몇 가지 갱신 방법이 있지만 여기서는 WHERE 구에 조건을 지정해 '있음'으로 갱신하는 경우와 '없음'으로 갱신하는 경우로 나누어 처리한다.
UPDATE sample551 SET a = '있음' WHERE ...
UPDATE sample551 SET a = '없음' WHERE ...
- 앞의 명령에서
WHERE부분을 살펴보자. 여기서 단순하게no = 1처럼 지정하는 방식으로는 처리할 수 없다. - 서브쿼리를 사용해 sample552에 행이 있는지부터 조사해야 한다.
- 그리고 '있음'인 경우, 행이 존재하는 경우에 대해 참으로 설정한다.
- 즉, 다음과 같이
EXISTS를 사용하면 조건에 맞는 행을 갱신할 수 있다.
EXISTS를 사용해 '있음'으로 갱신하기
UPDATE sample551 SET a = '있음' WHERE
EXISTS (SELECT * FROM sample552 WHERE no2 = no);
결과
| no | a |
|---|---|
| 1 | NULL |
| 2 | NULL |
| 3 | 있음 |
| 4 | NULL |
| 5 | 있음 |
- 서브쿼리 부분이
UPDATE의WHERE구로 행을 검색할 때마다 차례로 실행되는 느낌이다. - 서브쿼리의
WHERE구는no2 = no라는 조건식으로 되어 있다. - no2는 sample552의 열이고 no는 sample551의 열이다. 이때 no가 3과 5일 때만 서브쿼리가 행을 반환한다.
EXISTS술어에 서브쿼리를 지정하면 서브쿼리가 행을 반환할 경우에 참을 돌려준다.- 결과가 한 줄이라도 그 이상이라도 참이 된다. 반면 반환되는 행이 없을 경우에는 거짓이 된다.
2. NOT EXISTS
- '없음'의 경우, 행이 존재하지 않는 상태가 참이 되므로 이때는
NOT EXISTS를 사용한다. NOT을 붙이는 것으로 값을 부정할 수 있다.
NOT EXISTS를 사용해 '없음'으로 갱신하기
UPDATE sample551 SET a = '없음' WHERE
NOT EXISTS (SELECT * FROM sample552 WHERE no2 = no);
- 이처럼 서브쿼리를 이용해 다른 테이블의 상황을 판단하고
UPDATE로 갱신할 수 있었다. SELECT명령이나DELETE명령으로도 서브쿼리를 사용할 수 있다.
3. 상관 서브쿼리
- 서브쿼리에는 명령 안에 중첩구조로 된
SELECT명령이 존재한다. - 지금부터 '있음'으로 갱신하는
UPDATE명령을 다시 살펴본다.
UPDATE sample551 SET a = '있음' WHERE
EXISTS (SELECT * FROM sample552 WHERE no2 = no);
UPDATE명령(부모)에서WHERE구에 괄호로 묶은 부분이 서브쿼리(자식)가 된다.- 부모 명령에서는 sample551를 갱신한다.
- 자식 서브쿼리에서는 sample552 테이블의 no2 열 값이 부모의 no 열 값과 일치하는 행을 검색한다.
- 이처럼 부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것을
상관 서브쿼리라 부른다. - 앞서 23강에서 설명한
DELETE의 경우에는 상관 서브쿼리가 아니다. 상관 서브쿼리가 아닌 단순한 서브쿼리는 단독 쿼리로 실행할 수 있다.
DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);
- 하지만 상관 서브쿼리에서는 부모 명령과 연관되어 처리되기 때문에 서브쿼리 부분만을 따로 떼어내어 실행시킬 수 없다.
UPDATE sample551 SET a = '있음' WHERE
EXISTS (SELECT * FROM sample552 WHERE no2 = no);
SELECT * FROM sample552 WHERE no2 = no;
-> 에러: no2가 불명확하다.
테이블명 붙이기
-
지금은 sample551과 sample552는 각각 열이 no와 no2로 서로 다르기 때문에 no가 sample551의 열, no2가 sample552의 열인 것을 알 수 있다.
-
하지만 만약 두 열이 모두 같은 이름을 가진다면?
WHERE no = no라고 조건을 지정하면 제대로 동작할까? -
사실은 양쪽 테이블 모두 no라는 열로 되어있다면 잘 동작하지 않는다(대부분은 열이 애매하다는 내용의 에러가 발생한다).
- 다만
MySQL에서는 서브쿼리의WHERE no = no는WHERE sample552.no = sample552.no가 되어 조건식은 항상 참이 된다. 결과적으로sample551의 모든 행은 a열 값이 '있다'로 갱신된다.
- 다만
-
방금 언급한 사례가 정상적으로 처리되도록 하려면 열이 어느 테이블의 것인지 명시적으로 나타낼 필요가 있다.
-
테이블 지정은 간단하다. 열명 앞에
테이블명.을 붙이기만 하면 된다. -
예를 들어 no 열이 sample551의 것이라면
sample551.no라고 지정한다. -
마찬가지로 no2의 경우에는
sample552.no2로 지정한다. -
이것으로 sample551과 sample552가 열 이름이 같아도 제대로 구별되므로 문제 없이 실행할 수 있다.
열에 테이블명 붙이기
UPDATE sample551 SET a = '있음' WHERE
EXISTS (SELECT * FROM sample552 WHERE sample552.no2 = sample551.no);
4. IN
- 스칼라 값끼리 비교할 때는
=연산자를 사용한다. 다만 집합을 비교할 때는 사용할 수 없다. IN을 사용하면 집합 안의 값이 존재하는지를 조사할 수 있다.- 서브쿼리를 사용할 때
IN을 통해 비교하는 경우도 많다. - sample552에는 3과 5라는 값이 존재하는데, 서브쿼리를 사용하지 않고
WHERE구로 간단하게 처리한다면 다음과 같이 조건을 붙일 수 있다. - 이처럼 특정 열의 값이
무엇 또는(OR) 무엇이라는 조건식을 지정하는 경우IN을 사용하면 간단하게 지정할 수 있다.
WHERE no = 3 OR no = 5;
IN
열명 IN(집합)
IN에서는 오른쪽에 집합을 지정한다.- 왼쪽에 지정된 값과 같은 값이 집합 안에 존재하면 참을 반환한다.
- 집합은 상수 리스트를 괄호로 묶어 기술한다.
- 앞의
WHERE조건식을IN을 사용하도록 수정하면 다음과 같다. IN으로 지정한 값이 3과 5밖에 없어OR로 기술했을 때와 별 차이가 없는 것 같지만, 값을 여러개 지정할 경우에는 조건식이 상당히 깔끔해진다.
IN을 사용해 조건식 기술
SELECT * FROM sample551 WHERE no IN (3, 5);
- 한편, 집합 부분은 서브쿼리로도 지정할 수 있다. 상수 리스트 부분을 서브쿼리로 바꾸어 보면 다음과 같다.
IN의 오른쪽을 서브쿼리로 지정하기
SELECT * FROM sample551 WHERE no IN (SELECT no2 FROM sample552);
- 이 같은 경우 서브쿼리는 스칼라 서브쿼리가 될 필요는 없다.
IN에는 집합을 지정할 수 있기 때문에 이전에 스칼라 값을 설명할 때 언급한 패턴을 들자면 1과 2의 패턴으로 지정할 필요가 있다.- 반면 3과 4의 패턴에서는 열이 복수로 지정되므로 비교할 수 없다.
IN의 왼쪽에는 하나의 열이 지정되어 있기 때문이다. IN은 집합 안에 값이 포함되어 있으면 참이 된다. 반면NOT IN으로 지정하면 집합에 값이 포함되어 있지 않을 경우 참이 된다.
IN과 NULL
- 집계함수에서는 집합 안의
NULL값을 무시하고 처리했다. IN에서는 집합 안에NULL값이 있어도 무시하지는 않는다.- 다만
NULL = NULL을 제대로 계산할 수 없으므로IN을 사용해도NULL값은 비교할 수 없다. - 즉,
NULL을 비교할 때는IS NULL을 사용해야 한다. 또한NOT IN의 경우, 집합 안에NULL값이 있으면 설령 왼쪽 값이 집합 안에 포함되어 있지 않아도 참을 반환하지 않는다. - 그 결과는
불명(UNKNOWN)이 된다.MySQL에서 집합에NULL이 포함되어 있는 경우, 조건식IN은 왼쪽 값이 집합에 포함되어 있으면 참을, 그렇지 않으면NULL을 반환한다.NOT IN은 왼쪽 값이 집합에 포함되어 있으면 거짓을, 그렇지 않으면NULL을 반환한다.- 결국
NOT IN의 경우 집합에NULL이 포함되어 있다면 그 결괏값은 0건이 된다. NULL을 반환한다는 것은 비교할 수 없다는 것을 의미한다.- 왼쪽의 값이
NULL인 경우에도 오른쪽의 값과 관계없이 비교할 수 없으므로 조건식은 참 또는 거짓이 아닌NULL을 반환한다.