no exists, not in 을 이용한 조건에 만족하지 않는 것들 구하기, 둘의 차이점

보통 mysql에서는 외래키를 잡아놓고 두 외래키가 같은 것들로 조인을 해서 쓰는 방식이 많다. 필자도 보통 그런식으로만 해왔기 때문에 지금부터 쓸 포스트의 내용을 쓸일이 여태껏 거이 없었다. 하지만 종종 쓰다보니, 아무래도 이건 나중에 쓸때 또 찾을거 같은 내용 같기에 이렇게 글을 써본다.

일단 두 테이블 ids와 list가 있다고 하자.

mysql> select * from ids;
+-----+------+------+
| sid | year | id   |
+-----+------+------+
|   1 | 2007 | A    |
|   2 | 2008 | B    |
|   3 | 2010 | C    |
|   4 | NULL | D    |
+-----+------+------+
4 rows in set (0.00 sec)

mysql> select * from list;
+------+------+-------+
| year | id   | fee   |
+------+------+-------+
| 2008 | A    | 10000 |
| 2008 | A    | 15000 |
| 2008 | B    | 20000 |
| 2009 | B    | 10000 |
| 2009 | A    | 15000 |
| 2010 | C    | 10000 |
| 2010 | D    | 20000 |
+------+------+-------+
7 rows in set (0.00 sec)

위 두 테이블을 조인해서 아이디별로 자신의 year에 해당하는 list의 목록을 뽑아 오려고 한다면,

mysql> select * from ids left join list on ids.id = list.id where ids.year = list.year;
+-----+------+------+------+------+-------+
| sid | year | id   | year | id   | fee   |
+-----+------+------+------+------+-------+
|   2 | 2008 | B    | 2008 | B    | 20000 |
|   3 | 2010 | C    | 2010 | C    | 10000 |
+-----+------+------+------+------+-------+
2 rows in set (0.00 sec)

위와 같이 뽑아 올수 있다. 

위의 결과에서 A는 ids 테이블에 year해당하는 list의 값이 없기에 뽑아올수 없는데, 이런 A와 같은 목록을 뽑아오려면 not exists나 not in를 쓰면 된다.

mysql> select * from ids where not exists (select 1 from list where ids.id = list.id and ids.year = list.year );
+-----+------+------+
| sid | year | id   |
+-----+------+------+
|   1 | 2007 | A    |
|   4 | NULL | D    |
+-----+------+------+
2 rows in set (0.00 sec)

mysql> select * from ids where id not in (select id from list where ids.id = list.id and ids.year = list.year );
+-----+------+------+
| sid | year | id   |
+-----+------+------+
|   1 | 2007 | A    |
|   4 | NULL | D    |
+-----+------+------+
2 rows in set (0.00 sec)

하지만 여기서 한가지 알아둘것이 있는데, 위의 조건은 ids의 year과 list의 year이 같지 않은 id를 찾는것이다. 그렇다면, 아래와 같은 조건으로 not in을 쓸 수 있다.

mysql> select * from ids where year not in (select year from list where ids.id = list.id );
+-----+------+------+
| sid | year | id   |
+-----+------+------+
|   1 | 2007 | A    |
+-----+------+------+
1 row in set (0.00 sec)

바로 여기서 차이점이 발생한다. 조건만으로 따져볼때는 위의 조건이 틀려 보이진 않는다.
D의 year를 보면, ids에는 NULL로 되어 있고 list에는 2010로 되어 있기 때문에 NULL과 2010은 다르기 때문이다.
하지만, not in은 조건으로 들어가는 값이 NULL인 것을 결과에서 제외시켜 버린다. 

뭐 이건 이거대로 잘 활용한다면 괜찮은 팁이 될 수 있으나, not in으로 NULL까지 나오게 하고 싶다면 조건을 잘 주어야 할 것이다.