Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

IN

Q. SELECT all books written by 'Carver' or 'Lahiri' or 'Smith'.
먼저 이전에 배운 논리연산자로 구해보자.

mysql> SELECT title, author_lname 
	FROM books 
	WHERE author_lname = 'Carver' 
   	OR author_lname = 'Lahiri' 
    	OR author_lname = 'Smith';
+-----------------------------------------------------+--------------+
| title                                               | author_lname |
+-----------------------------------------------------+--------------+
| The Namesake                                        | Lahiri       |
| Interpreter of Maladies                             | Lahiri       |
| Just Kids                                           | Smith        |
| What We Talk About When We Talk About Love: Stories | Carver       |
| Where I'm Calling From: Selected Stories            | Carver       |
+-----------------------------------------------------+--------------+
5 rows in set (0.00 sec)

'IN' makes it much easier and shorter

mysql> SELECT title, author_lname 
	FROM books WHERE author_lname 
    	IN('Carver','Lahiri','Smith');
+-----------------------------------------------------+--------------+
| title                                               | author_lname |
+-----------------------------------------------------+--------------+
| The Namesake                                        | Lahiri       |
| Interpreter of Maladies                             | Lahiri       |
| Just Kids                                           | Smith        |
| What We Talk About When We Talk About Love: Stories | Carver       |
| Where I'm Calling From: Selected Stories            | Carver       |
+-----------------------------------------------------+--------------+
5 rows in set (0.00 sec)

NOT IN

Q. SELECT all books not published in even number.(start from 2000 to 2016) 2000년대 이상부터는 짝수인 책을 제외하고 출력하라.
먼저 논리연산자로 할 수 있다.

mysql> SELECT title, released_year FROM books 
    -> WHERE released_year != 2000
    -> AND released_year != 2002
    -> AND released_year != 2004
    -> AND released_year != 2006
    -> AND released_year != 2008
    -> AND released_year != 2010
    -> AND released_year != 2012
    -> AND released_year != 2014
    -> AND released_year != 2016;
+-----------------------------------------------------+---------------+
| title                                               | released_year |
+-----------------------------------------------------+---------------+
| The Namesake                                        |          2003 |
| Interpreter of Maladies                             |          1996 |
| The Circle                                          |          2013 |
| A Heartbreaking Work of Staggering Genius           |          2001 |
| Coraline                                            |          2003 |
| What We Talk About When We Talk About Love: Stories |          1981 |
| Where I'm Calling From: Selected Stories            |          1989 |
| White Noise                                         |          1985 |
| Cannery Row                                         |          1945 |
| Consider the Lobster                                |          2005 |
| fake_book                                           |          2001 |
| Lincoln In The Bardo                                |          2017 |
+-----------------------------------------------------+---------------+
12 rows in set (0.00 sec)

NOT IN을 사용하면 더 짧고 간단하게 표현가능하다.

mysql> SELECT title, released_year FROM books
    -> WHERE released_year NOT IN
    -> (2000,2002,2004,2006,2008,2010,2012,2014,2016);
+-----------------------------------------------------+---------------+
| title                                               | released_year |
+-----------------------------------------------------+---------------+
| The Namesake                                        |          2003 |
| Interpreter of Maladies                             |          1996 |
| The Circle                                          |          2013 |
| A Heartbreaking Work of Staggering Genius           |          2001 |
| Coraline                                            |          2003 |
| What We Talk About When We Talk About Love: Stories |          1981 |
| Where I'm Calling From: Selected Stories            |          1989 |
| White Noise                                         |          1985 |
| Cannery Row                                         |          1945 |
| Consider the Lobster                                |          2005 |
| fake_book                                           |          2001 |
| Lincoln In The Bardo                                |          2017 |
+-----------------------------------------------------+---------------+
12 rows in set (0.00 sec)

그리고 2002, 2004이런식으로 열거하는 것이 아니라, 짝수번호를 전부 제거하고 싶다면 이렇게도 가능하다. 먼저 %(나머지) 들을 구해보자. 2로 나누어서 나머지가 0이면 짝수인 거고 나머지가 0이 아니면 홀수다.

mysql> SELECT released_year, released_year%2 FROM books;
+---------------+-----------------+
| released_year | released_year%2 |
+---------------+-----------------+
|          2003 |               1 |
|          2016 |               0 |
|          1996 |               0 |
|          2012 |               0 |
|          2013 |               1 |
|          2000 |               0 |
|          2010 |               0 |
|          2001 |               1 |
|          2003 |               1 |
|          1981 |               1 |
|          1989 |               1 |
|          1985 |               1 |
|          1945 |               1 |
|          2004 |               0 |
|          2005 |               1 |
|          2014 |               0 |
|          2001 |               1 |
|          2017 |               1 |
+---------------+-----------------+
18 rows in set (0.00 sec)

이걸 이용하면

mysql> SELECT title, released_year
    -> FROM books
    -> WHERE released_year%2 != 0;
+-----------------------------------------------------+---------------+
| title                                               | released_year |
+-----------------------------------------------------+---------------+
| The Namesake                                        |          2003 |
| The Circle                                          |          2013 |
| A Heartbreaking Work of Staggering Genius           |          2001 |
| Coraline                                            |          2003 |
| What We Talk About When We Talk About Love: Stories |          1981 |
| Where I'm Calling From: Selected Stories            |          1989 |
| White Noise                                         |          1985 |
| Cannery Row                                         |          1945 |
| Consider the Lobster                                |          2005 |
| fake_book                                           |          2001 |
| Lincoln In The Bardo                                |          2017 |
+-----------------------------------------------------+---------------+
11 rows in set (0.00 sec)

여기서는 11개 칼럼이 나온 이유는 뭐냐면 NOT IN 조건에서는 2000년대부터 2016년도 까지만 짝수가 없는 거를 선택하는 건데 짝수를 전부 제거해줘서 1996년도에 나온 책까지 제거해버려서 그렇다.

서브쿼리를 사용해서 해결해보면

mysql> SELECT title, released_year 
	FROM books 
    	WHERE released_year 
        NOT IN (SELECT released_year FROM books WHERE released_year >= 2000 AND released_year%2 = 0);
+-----------------------------------------------------+---------------+
| title                                               | released_year |
+-----------------------------------------------------+---------------+
| The Namesake                                        |          2003 |
| Interpreter of Maladies                             |          1996 |
| The Circle                                          |          2013 |
| A Heartbreaking Work of Staggering Genius           |          2001 |
| Coraline                                            |          2003 |
| What We Talk About When We Talk About Love: Stories |          1981 |
| Where I'm Calling From: Selected Stories            |          1989 |
| White Noise                                         |          1985 |
| Cannery Row                                         |          1945 |
| Consider the Lobster                                |          2005 |
| fake_book                                           |          2001 |
| Lincoln In The Bardo                                |          2017 |
+-----------------------------------------------------+---------------+
12 rows in set (0.00 sec)

이렇게 해결해줬다.

이제 서브쿼리를 사용하지 않고 해결해보면

mysql> SELECT title, released_year 
	FROM books 
    	WHERE released_year < 2000 || (released_year >= 2000 && released_year %2 != 0);
+-----------------------------------------------------+---------------+
| title                                               | released_year |
+-----------------------------------------------------+---------------+
| The Namesake                                        |          2003 |
| Interpreter of Maladies                             |          1996 |
| The Circle                                          |          2013 |
| A Heartbreaking Work of Staggering Genius           |          2001 |
| Coraline                                            |          2003 |
| What We Talk About When We Talk About Love: Stories |          1981 |
| Where I'm Calling From: Selected Stories            |          1989 |
| White Noise                                         |          1985 |
| Cannery Row                                         |          1945 |
| Consider the Lobster                                |          2005 |
| fake_book                                           |          2001 |
| Lincoln In The Bardo                                |          2017 |
+-----------------------------------------------------+---------------+
12 rows in set, 2 warnings (0.00 sec)

이렇게 해결해줬다.