[MSSQL] in,not in, exists, not exists 내부 동작로직 및 예제

 in,not in, exists, not exists 함수를 제대로 모르고 사용하게 되면

 원하는 결과가 아닌 다른 결과를 가져와 프로그램 오류를 발생시킬 수 있다.

 내부로직을 예제를 통해서 확인하고 제대로 활용해보도록 하자

--과일 테이블 생성 및 데이터 삽입
create table fruit(
number int,
fruit varchar(50)
)
GO

INSERT INTO fruit VALUES (1,'사과')
INSERT INTO fruit VALUES (2,'바나나')
INSERT INTO fruit VALUES (3,NULL)
INSERT INTO fruit VALUES (NULL,'수박')
INSERT INTO fruit VALUES (4,'망고')
INSERT INTO fruit VALUES (5,'체리')

--색상 테이블 생성 및 데이터 삽입
create table color(
number int,
color varchar(50)
)
GO

INSERT INTO color VALUES (1,'red')
INSERT INTO color VALUES (2,'green')
INSERT INTO color VALUES (3,NULL)
INSERT INTO color VALUES (NULL,'black')

--테이블 값 확인
select * from fruit
select * from color

---

IN 함수

select * from fruit where number in(select number from color)

 

테이블 접근 순서 : color -> fruit

in 함수는 소괄호의 세부쿼리 값을 포함할 경우 결과를 출력한다.

내부적으로 or로 동작하여 하나라도 참이면 결과를 출력한다

NULL과의 비교연산은 항상 UNKNOWN 값을 반환하기 때문에 NULL 값은 출력되지 않는다.

내부적으로 동작하는 쿼리 : select * from fruit where number=1 or number=2 or number=3 or number=NULL

---

NOT IN 함수

select * from fruit where number not in(select number from color)

테이블 접근 순서 : color -> fruit

not in 함수는 소괄호의 세부쿼리 값을 포함하지 않는 경우 결과를 출력한다.

내부적으로 AND로 동작하여 모두 참이면 결과를 출력한다

NULL과의 비교연산은 항상 UNKNOWN 값을 반환하기 때문에 하나라도 NULL 값이 있으면 모든 결과를 출력하지 않는다.

내부적으로 동작하는 쿼리 : select * from fruit where number!=1 and number!=2 and number!=3 and number!=NULL

 

따라서 원하는 결과를 출력하기 위해서는 소괄호의 세부쿼리 값에서 NULL값을 제거해 주면 된다.

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

---

 

EXISTS 함수

select * from fruit where  exists (select number from color)

테이블 접근 순서 :  fruit -> color

EXISTS 함수는 메인 테이블 기준으로 서브쿼리에 대한 결과가 존재할 때 해당 레코드를 출력한다.

EXISTS 하위의 서브쿼리 구문은 결과값이 존재하기 때문에 fruit 테이블의 모든 결과를 출력한다.

 

--동일한 결과를 도출함
select * from fruit where  exists (select number from color where number=1)
select * from fruit where  exists (select number from color where number=2)
select * from fruit where  exists (select number from color where number=3)
select * from fruit where  exists (select number from color where number is null)
select * from fruit where  exists (select number from color where number is not null)
select * from fruit where  exists (select 1)

 

 

따라서 원하는 결과를 출력하기 위해서는 아래와 같이 연관 관계를 알 수 있도록 쿼리수정이 필요하다.(NULL과의 비교연산은 항상 UNKNOWN 이라 결과출력 제외)

select * from fruit a where  exists (select number from color b where a.number=b.number)

---

NOT EXISTS 함수

select * from fruit where not exists (select number from color)

테이블 접근 순서 :  fruit -> color

NOT EXISTS 함수는 메인 테이블 기준으로 서브쿼리에 대한 결과가 존재 하지 않을 경우 해당 레코드를 출력한다.

NOT EXISTS 하위의 서브쿼리 구문은 결과값이 존재하기 때문에 fruit 테이블의 모든 결과를 출력하지 않는다.

 

 

따라서 원하는 결과를 출력하기 위해서는 아래와 같이 연관 관계를 알수 있도록 쿼리수정이 필요하다.

NULL에 대한 비교연산은 항상 UNKNOWN 값을 반환하므로 해당 쿼리의 결과가 존재하지 않게 되어 NULL 컬럼도 출력된다.

select * from fruit a where not exists (select number from color b where a.number=b.number)