상관 서브쿼리

서브쿼리의 일종인 ‘상관 서브쿼리’를 EXISTS 술어로 조합시켜서 서브쿼리를 사용하는 방법

• EXISTS (SELECT명령)

EXISTS 술어를 사용하면 서브쿼리가 반환하는 결괏값이 있는지를 조사할 수 있다.

• EXISTS를 사용하는 경우에는 서브쿼리가 반드시 스칼라 값을 반환할 필요 없다.
• EXISTS는 단지 반환된 행이 있는지를 확인해보고 값이 있으면 true, 없으면 false를 반환하므로 어떤 패턴이라도 상관없다.

 

EXISTS

서브쿼리를 이용해 검색할 때 ‘데이터가 존재하는지 아닌지’ 판별하기 위해서 조건을 지정할 때 사용할 수 있다.

SELECT * FROM sample551;

no	a (char)
1	null
2	null
3	null
4	null
5	null

SELECT * FROM sample552;

no2

3

5

sample551 table의 a열을 UPDATE 하려고 한다.

• sample552에 no 열의 값과 같은 행이 있으면 ‘있음’ 없으면 ‘없음’ 이라는 값으로 갱신

 
WHERE 구에 조건을 지정해 ‘있음’으로 갱신하는 경우와 ‘없음’으로 갱신하는 경우로 나누어 처리

UPDATE sample551 SET a = '있음' WHERE ...
UPDATE sample551 SET a = '없음' WHERE ...

where no = 1 처럼 지정하는 방식으로 처리할 수 없기 때문에 서브쿼리를 사용해 행이 있는지 확인해야 한다.

• EXISTS를 사용해 조건에 맞은 행을 갱신할 수 있다.

UPDATE sample551 SET a = '있음' WHERE
 EXISTS (SELECT * FROM sample552 where no2 = no);

서브쿼리의 WHERE 구는 no2 = no 라는 조건식으로 되어 있다.

• no2는 sample 552의 열이고 no는 sample551의 열이다. (no가 3과 5일 때만 서브쿼리가 행을 반환)

EXISTS 술어에 서브쿼리를 지정하면 서브쿼리가 행을 반환할 경우에 참을 돌려 준다.
- 결과가 한 줄이라도 그 이상이라도 '참' 반환되는 행이 없으면 '거짓'

 
 

NOT EXISTS

‘없음’의 경우, 행이; 존재하지 않는 상태가 참이 되므로 이때는 NOT EXISTS를 사용한다.

• NOT을 붙이는 것으로 값을 부정할 수 있다.

UPDATE sample551 SET a = '없음' WHERE
	NOT EXISTS (SELECT * FROM sample552 WHERE no2 = no);

서브쿼리를 이용해 다른 테이블의 상황을 판단하고 UPDATE로 갱신할 수 있다.

SELECT 명령이나 DELETE 명령으로도 서브쿼리를 사용할 수 있다.

 

---

상관 서브쿼리

서브쿼리에는 명령 안에 중첩구조로 된 SELECT 명령이 존재한다.
 
위의 예제 UPDATE 명령을 다시 살펴보자

UPDATE sample551 SET a = '있음' WHERE
 EXISTS (SELECT * FROM sample552 WHERE no2 = no);

UPDATE 명령(부모)에서 WHERE 구에 괄호로 묶은 부분이 서브쿼리(자식)이 된다.

- 부모 명령에서는 sample551를 갱신한다.
- 자신인 서브쿼리에서는 sample552 테이블의 no2 열 값이 부모의 no 열 값과 일치하는 행을 검색한다.

부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것을 ‘상관 서브쿼리’라 부른다.

• no2 = no 부분!

 
이 코드는 상관 서브쿼리가 아니다.

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가 551 no2가 552의 열인 것을 알 수 있다.

• 같은 데이터라면 같은 이름으로 지정하는 편이 자연스러우나 예제라서 no, no2로 구분

두 열이 모두 같은 이름으로 ‘WHERE no = no’ 라고 조건을 지정하면 재대로 동작할까?

• 양쪽 테이블 모두 no라는 열로 되어있다면 잘 동작하지 않는다.
  • (대부분은 열이 애매하다는 내용의 에러가 발생한다.)
    • MySQL에서는 서브쿼리 ‘WHERE no = no’는 ‘WHERE sample552.no = sample552.no’가 되어 조건식은 항상 참이 된다. (sample551의 모든 행은 a열 값이 ‘있다’로 갱신)

 
정상적으로 처리되도록 하는 방법은?
어느 테이블의 것인지 명시적으로 나타내주면 된다.

UPDATE sample551 SET a = '있음' WHERE
 EXISTS (SELECT * FROM sample552 WHERE sample552.no = sample551.no);

• ‘테이블명.열명’을 사용하면 된다. (테이블의 열 이름이 같아도 제대로 구별되므로 문제 없이 실행 가능)

 

IN

스칼라 값끼리 비교할 때는 = 연산자를 사용한다.

• 집합을 비교할 때는 사용할 수 없다.

IN을 사용하면 집합 안의 값이 존재하는지를 조사할 수 있다.

• 서브쿼리를 사용할 때 IN을 통해 비교하는 경우도 많다.

sample552에는 3과 5라는 값이 존재한다.

• 서브쿼리를 사용하지 않고 WHERE 구로 간단하게 처리한다면 다음과 같이 조건을 붙일 수 있다.
  • 특정 열의 값이 ‘무엇 또는(OR) 무엇’ 이라는 조건식을 지정하는 경우 IN을 사용하면 간단하게 지정할 수 있다.
    
    

WHERE no = 3 OR no = 5;

IN을 사용하면

SELECT * FROM sample551 WHERE no IN(3, 5);

IN에서는 오른쪽에 집합을 지정하고, 왼쪽에 지정된 값과 같은 값이 집합 안에 존재하면 참을 반환한다.

• 집합은 상수 리스트로 괄호로 묶어 기술한다.
• 값을 여러개 지정할 경우에 조건식이 깔끔하다.

집합 부분은 서브쿼리로도 지정할 수 있다.

SELECT * FROM sample551 WHERE no IN
	(SELECT no2 FROM sample552);

IN에는 집합을 지정할 수 있기 때문에 서브쿼리는 스칼라 서브쿼리가 될 필요는 없다.

• 하나의 열에 행이 여러개가 존재하는 것은 비교를 할 수 있어 가능하지만 열이 복수로 지정되면 비교할 수 없다.
• IN은 집합 안에 값이 포함되어 있으면 참이며 NOT IN으로 지정하면 값이 포함되어 있지 않을 경우 참이다.

 
IN과 NULL
집계함수에서는 집합 안의 NULL 값을 무시하고 처리했지만, IN에서는 집합 안에 NULL 값이 있어도 무시하지는 않는다.

• 다만 NULL = NULL을 제대로 계산할 수 없으므로 IN을 사용해도 NULL 값은 비교할 수 없다.
• NULL을 비교할 때는 IS NULL을 사용해야 한다.

NOT IN의 경우, 집합 안에 NULL 값이 있으면 설령 왼쪽 값이 집합 안에 포함되어 있지 않아도 참을 반환하지 않는다.

• 결과 불명(UNKNOWN)이 된다.

 
MySQL에서 집합에 NULL이 포함되어 있는 경우
 
1. IN은 왼쪽 값이 집합에 포함되어 있으면 참을, 그렇지 않으면 NULL을 반환한다.
2. NOT IN은 왼쪽 값이 집합에 포함되어 있으면 거짓을, 그렇지 않으면 NULL을 반환한다.

• 결국 NOT IN의 경우 집합에 NULL이 포함되어 있다면 그 결괏값은 0건이 된다.

NULL을 반환한다는 것은 비교할 수 없다는 것을 의미한다.

• 왼쪽의 값이 NULL인 경우에도 오른쪽의 값과 관계없이 비교할 수 없으므로, 조건식은 참 또 거짓이 아닌 NULL을 반환한다.