With SQL, we can use nested queries, where output of one query is provided as an input for another query.
Let us understand nested queries using a simple example. Let us say, we would like to see availability of a given item at all the stores; the inventory table keep records of items using their codes and not names. If we just know the name of the toy, then we can use the WHERE clause to get the code for the toy from the items table and then pass the output code as input to another query that runs on inventory table.
Thus, the query, "SELECT * FROM inventory, items WHERE items.item_name = "Elmo" AND items.item_code = inventory.item_code;" would provide inventory details for Elmo toys. However, SQL provides another option of using nested query to achieve the same result!
mysql> SELECT item_code FROM items WHERE item_name = "Elmo";
+-----------+
| item_code |
+-----------+
| 1003 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM inventory WHERE item_code =
-> (SELECT item_code FROM items WHERE item_name = "Elmo");
+----------+-----------+---------------+
| store_id | item_code | item_quantity |
+----------+-----------+---------------+
| 1 | 1003 | 11 |
| 2 | 1003 | 11 |
| 3 | 1003 | 3 |
+----------+-----------+---------------+
3 rows in set (0.00 sec)
In the above query, we use two queries, where the output of the inner query is fed as an input to the outer query. Since the output of the inner query, is 1003, the overall query translates to "SELECT * FROM inventory WHERE item_code = 1003;",
However, there is an important caveat! It can happen that the output of the nested query can be more than one value; in other words, the output may not be a scalar. If we were to use the WHERE clause, then this would mean that we have a list of column being compared against "WHERE item_code =" and SQL would throw the error:
mysql> SELECT item_code FROM items WHERE item_name LIKE "%Man";
+-----------+
| item_code |
+-----------+
| 1005 |
| 1006 |
| 1004 |
+-----------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM inventory WHERE item_code = (SELECT item_code FROM items
-> WHERE item_name LIKE "%Man");
ERROR 1242 (21000): Subquery returns more than 1 row
To handle such use-cases, SQL provides additional keywords like IN, NOT IN, ANY, SOME, and ALL. In this case, we can use the "IN" clause to say that as long as the item_name is in the set {1005,1006,1004}, we would like to print inventory details for all those items.
mysql> SELECT * FROM inventory WHERE item_code IN
-> (SELECT item_code FROM items WHERE item_name LIKE "%Man");
+----------+-----------+---------------+
| store_id | item_code | item_quantity |
+----------+-----------+---------------+
| 2 | 1006 | 17 |
| 3 | 1005 | 22 |
| NULL | 1006 | 25 |
| NULL | 1004 | 10 |
+----------+-----------+---------------+
4 rows in set (0.00 sec)
Let us understand the other keywords as well!
NOT IN is opposite of IN. With NOT IN, we are indicating that we would like to get details about all the items except those with item_code as 1005 or 1006 or 1004.
mysql> SELECT * FROM inventory WHERE item_code NOT IN
-> (SELECT item_code FROM items WHERE item_name LIKE "%Man");
+----------+-----------+---------------+
| store_id | item_code | item_quantity |
+----------+-----------+---------------+
| 1 | 1003 | 11 |
| 1 | 1007 | 7 |
| 2 | 1003 | 11 |
| 2 | 1001 | 2 |
| 3 | 1003 | 3 |
| NULL | 1001 | 40 |
| NULL | 1002 | 14 |
+----------+-----------+---------------+
7 rows in set (0.00 sec)
Lastly, keyword ALL can be used when we are interested in finding items such that their item_codes are either larger than or smaller than the set. Thus, with ALL there is a notion of either the maximum or the minimum value. In the following example, with " > ALL" we are saying that the item_code has to be greater than the maximum value in the set ({1005,1006,1004}). And, with " < ALL" we are saying that the item_code has to be less than the minimum value in the set ().
mysql> SELECT * FROM inventory WHERE item_code > ALL
-> (SELECT item_code FROM items WHERE item_name LIKE "%Man");
+----------+-----------+---------------+
| store_id | item_code | item_quantity |
+----------+-----------+---------------+
| 1 | 1007 | 7 |
+----------+-----------+---------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM inventory WHERE item_code < All
-> (SELECT item_code FROM items WHERE item_name LIKE "%Man");
+----------+-----------+---------------+
| store_id | item_code | item_quantity |
+----------+-----------+---------------+
| 1 | 1003 | 11 |
| 2 | 1003 | 11 |
| 2 | 1001 | 2 |
| 3 | 1003 | 3 |
| NULL | 1001 | 40 |
| NULL | 1002 | 14 |
+----------+-----------+---------------+
6 rows in set (0.00 sec)
Keyword ANY is in someways opposite to that of ALL. With " > ANY", we are saying that as long as the item_code is greater than any item code in the set ({1005,1006,1004}) and this translates to " > 1004". Likewise, with " < ANY" translates to " < 1007".
mysql> SELECT * FROM inventory WHERE item_code > ANY
-> (SELECT item_code FROM items WHERE item_name LIKE "%Man");
+----------+-----------+---------------+
| store_id | item_code | item_quantity |
+----------+-----------+---------------+
| 1 | 1007 | 7 |
| 2 | 1006 | 17 |
| 3 | 1005 | 22 |
| NULL | 1006 | 25 |
+----------+-----------+---------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM inventory WHERE item_code < ANY
-> (SELECT item_code FROM items WHERE item_name LIKE "%Man");
+----------+-----------+---------------+
| store_id | item_code | item_quantity |
+----------+-----------+---------------+
| 1 | 1003 | 11 |
| 2 | 1003 | 11 |
| 2 | 1001 | 2 |
| 3 | 1003 | 3 |
| 3 | 1005 | 22 |
| NULL | 1001 | 40 |
| NULL | 1002 | 14 |
| NULL | 1004 | 10 |
+----------+-----------+---------------+
8 rows in set (0.00 sec)