상관 서브쿼리

banjjoknim·2021년 2월 27일
0

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|있음|

  • 서브쿼리 부분이 UPDATEWHERE 구로 행을 검색할 때마다 차례로 실행되는 느낌이다.
  • 서브쿼리의 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 = noWHERE 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을 반환한다.

profile
꿈꾸는 개발자

0개의 댓글