[mysql] not in , in , exists, not exists 예시

 

food, color 두개의 table 이 있다. 

이때 not in과 in이 어떻게 행동하는지 알아보자 

 

 

먼저 알아야하는 것은 null연산은 unkown이다.

 

 

먼저

IN

in 은 내부쿼리를 통해 list를 만들고 외부쿼리의 값과 비교한다. ( 내부쿼리 -> 외부쿼리)

 

not IN은 unkown을 미포함한다.

"=" 연산과 같다. 

 

 

select * from food where number in (select number from color);

먼저 내부쿼리를 통해 list를 만든다. color의 number이므로 {1, 2, 3, 4, null }이다. 

이를 food 테이블의 number와 비교한다. 

null과 null의 연산은 unkown이고, in에서는 미포함이므로 null은 출력되지않는다. 

 

즉,  food의 number( 1, 2 ,3 ,4 ,5, null) 중 5, null 은 포함되지않으므로 number가 1,2,3,4 인 튜플만 출력된다. 

 

 

 

 

NOT IN

 

select * from food where number not in (select number from color);

 

이 select 문을 보면

먼저 color에서 number을 뽑아서 list를 만든다음 food의 number에 없는 것을 찾아 출력하겠구나 라는 생각이든다. 

 

list를 생각해보자

{ 1 , 2 , 3 , 4 , null } 이일것이다.

 

food의 number는 1 , 2 , 3 , 4 , 5 , null 이다. 

처음 드는 생각은 5가 list에 포함되어있지않으니까 5, bread가 출력되려나! 이다. 

 

하지만 틀렸다. 

사진처럼 아무것도 출력되지않는다. 

 

 

그 이유를 한문장으로 설명하면 null 연산은 unkown이 되고, not in 은 unknown을 미포함하기 때문이다. 

 

[in은 요소간에 각각 비교연산을 한다. => food의 number값이 list의 모든 요소들과 일치여부를 확인한다. ]

 

list의 null이 존재하기 때문에 각각 비교할때, null 과의 연산이 포함된다. null 과의 연산은 unkown이 되고,

in은 unkown을 포함하지않으므로(in은 '=' 과 같은데 not in은 !=을 의미한다고 생각하면 != 은 unknown과 다르므로)

모든 튜플이 출력되지않는것이다. 

 

 

이것을 생각대로 출력하고 싶다면  not in 쿼리 안에서 list에 null이 들어가지 않도록 

where number is not null 코드를 추가시키면 된다.  

 

select * from food where number not in (select number from color where number is not null);

 

 

 

 

---

 

 

EXISTS 

 

exists 은 외부쿼리를 통해 레코드를 가져와 내부쿼리에 값이 "존재하는지" 확인한다. ( 외부쿼리 -> 내부쿼리)

 

NOT EXISTS은 unkown을 포함한다.

 

 

 

 

select * from food where exists (select number from color);

내부쿼리에 비교구문(where절)이 없어서 외부쿼리의 값들이 모두 출력된다. 

 

 

 

 

select * from food where exists (select number from color where food.number = color.number);

내부쿼리에 where절을 추가하여 조건을 주었다. where절은 number를 비교하여 '=' 인것을 조건으로 만들었다. 

'=' 과 null의 연산값인 unknown 은 다르므로 서브쿼리 결과는 null값을 포함하지않는다. 

 

즉 , 1, 2, 3, 4 가 서브쿼리이고 exists는 존재여부를 따지므로 food의 1, 2, 3, 4가 출력된다. 

 

 

 

NOT EXISTS 

select * from food where not exists (select number from color where food.number = color.number);

 

food table에서 number를 가져오면  1 , 2 , 3 , 4 , 5 , null 이고, 서브쿼리에는 1 ,2, 3, 4 이 존재한다.

not exists이므로 서브쿼리에 값이 존재하지않는 튜플을 찾는다. 

 

{ 1 , 2 , 3 , 4 , 5 , null  } 에서 1, ,2, 3, 4 는 서브쿼리에 존재하지 않은 number는 5, null이다. 

(null을 비교하면 unknown 이고 not exists는 unknown을 포함한다. 즉 food의 number가 null인 튜플도 출력한다.)

[exists는 그냥 존재여부를 따진다 라고 생각하면 편하다.]

 

 

 

참고 블로그 : 

https://doorbw.tistory.com/222

[MS SQL Server] #11_ IN / EXISTS / NOT IN / NOT EXISTS 비교

 

지적, 조언 환영합니다.

 

 

 

 

 

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."