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

&&

Q. SELECT books written by Dave Eggers, published after the year 2010

mysql> SELECT title, author_fname, author_lname, released_year 
	FROM books 
    	WHERE author_fname = 'Dave' AND author_lname = 'Eggers' AND released_year >= 2010;
+----------------------------------+--------------+--------------+---------------+
| title                            | author_fname | author_lname | released_year |
+----------------------------------+--------------+--------------+---------------+
| A Hologram for the King: A Novel | Dave         | Eggers       |          2012 |
| The Circle                       | Dave         | Eggers       |          2013 |
+----------------------------------+--------------+--------------+---------------+
2 rows in set (0.00 sec)

여기서 AND는 &&로 바꿔 줄 수 있다. 보통의 다른 프로그래밍 언어에서 쓰는 &&와 비슷(사실 거의 같음).

mysql> SELECT title, author_fname, author_lname, released_year 
	FROM books 
    	WHERE author_fname = 'Dave' && author_lname = 'Eggers' && released_year >= 2010;
+----------------------------------+--------------+--------------+---------------+
| title                            | author_fname | author_lname | released_year |
+----------------------------------+--------------+--------------+---------------+
| A Hologram for the King: A Novel | Dave         | Eggers       |          2012 |
| The Circle                       | Dave         | Eggers       |          2013 |
+----------------------------------+--------------+--------------+---------------+
2 rows in set, 2 warnings (0.00 sec)

논리 연산자이기 때문에 이런것도 된다.

mysql> SELECT 1 < 5 && 7 = 9;
+----------------+
| 1 < 5 && 7 = 9 |
+----------------+
|              0 |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT -10 > -20 && 0 <= 0;
+---------------------+
| -10 > -20 && 0 <= 0 |
+---------------------+
|                   1 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 54 <= 54 AND 'a' = 'A';
+------------------------+
| 54 <= 54 AND 'a' = 'A' |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

|| (OR)

다른 프로그래밍 언어에서 쓰는 ||(OR)과 비슷하다(사실 거의 똑같다).

먼저 &&를 쓴 SELECT문

mysql> SELECT title, author_lname, released_year FROM books WHERE author_lname = 'Eggers' &&  released_year > 2010;
+----------------------------------+--------------+---------------+
| title                            | author_lname | released_year |
+----------------------------------+--------------+---------------+
| A Hologram for the King: A Novel | Eggers       |          2012 |
| The Circle                       | Eggers       |          2013 |
+----------------------------------+--------------+---------------+
2 rows in set, 1 warning (0.00 sec)

||를 쓴 SELECT문

mysql> SELECT title, author_lname, released_year FROM books WHERE author_lname = 'Eggers' || released_year > 2010;
+-------------------------------------------+--------------+---------------+
| title                                     | author_lname | released_year |
+-------------------------------------------+--------------+---------------+
| Norse Mythology                           | Gaiman       |          2016 |
| A Hologram for the King: A Novel          | Eggers       |          2012 |
| The Circle                                | Eggers       |          2013 |
| A Heartbreaking Work of Staggering Genius | Eggers       |          2001 |
| 10% Happier                               | Harris       |          2014 |
| Lincoln In The Bardo                      | Saunders     |          2017 |
+-------------------------------------------+--------------+---------------+
6 rows in set, 1 warning (0.00 sec)

다른 프로그래밍에서의 &&(AND)나 ||(OR)과 같이, &&는 Both Sides Must be TRUE여야 True가 되고 한쪽이라도 FALSE이면 False가 된다. ||는 Only one side must be true, 한쪽만 True이면 True이다.

실험 몇개 해보자.

• 40 <= 100 || -2 > 0 앞에는 true지만 뒤에가 false. 하지만 ||이니깐 true
• 10 > 5 || 5 = 5 앞, 뒤 둘다 true
• 'a' = 5 || 3000 > 2000 앞은 false지만 뒤가 true. ||니깐 true

mysql> SELECT 40 <= 100 || -2 > 0;
+---------------------+
| 40 <= 100 || -2 > 0 |
+---------------------+
|                   1 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 10 > 5 || 5 = 5;
+-----------------+
| 10 > 5 || 5 = 5 |
+-----------------+
|               1 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 'a' = 5 || 3000 > 2000;
+------------------------+
| 'a' = 5 || 3000 > 2000 |
+------------------------+
|                      1 |
+------------------------+
1 row in set, 2 warnings (0.00 sec)

++ &&나 ||를 쓰면 2 rows in set, 1 warning (0.00 sec)이런식의 경고문구를 함께 볼 수 있다. 그래서 SHOW WARNINGS;를 통해 경고문구를 봐보면

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                            |
+---------+------+------------------------------------------------------------------------------------+
| Warning | 1287 | '&&' is deprecated and will be removed in a future release. Please use AND instead |
+---------+------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-------------------

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
+---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

이런식으로 &&나 ||는 곧 deprecated되므로 될수있으면 AND나 OR로 대신해서 쓰라고 나오고 있다.