[SQL First Step] 23. Subquery

Subquery

Subquery is a lower part SELECT command in SQL command. It is usually seperated with parentheses.

The following is an image of explaining the usefulness of subquery. Two command is corporated into one command.

Subquery is usually used in WHERE phrase.

Subquery in DELETE

As the image above, you can delete a row with only one command instead of using two commands separately. The following command checks the minimum value and delete it at once.

DELETE FROM sample54 WHERE a=(SELECT MIN(a) FROM sample54);

However, the command above does not work in MySQL. Using the same table in subquery when updating or deleting is not allowed in MySQL. Instead, use DELETE FROM sample54 WHERE a=(SELECT a FROM (SELECT MIN(a) AS a FROM sample54) AS x);

Four Types of Subquery Pattern

When using subquery, be aware of returning type of SELECT command. There are 4 types.

1. Scalar(one value): SELECT MIN(a) FROM sample54;
2. Multiple rows with one column: SELECT no FROM sample54;
3. One row with multiple columns: SELECT MIN(a), MAX(no) FROM sample54;
4. Multiple rows and multiple columns: SELECT no, a FROM sample54;

Among the 4 types, scalar(type 1) is unique. It returns only one value and easy to use as a subquery. In many cases, you would compare two values equal or not when using subquery, and scalar type is useful.

Subquery in SELECT

When using subquery in SELECT phrase, scalar subquery is needed. See the following example.

mysql> SELECT
    -> (SELECT COUNT(*) FROM sample51) AS sq1,
    -> (SELECT COUNT(*) FROM sample54) AS sq2;
+------+------+
| sq1  | sq2  |
+------+------+
|    5 |    3 |
+------+------+
1 row in set (0.01 sec)

The number of rows on table sample51 and sample 54 consist of each subquery. The upper part of SELECT does not have FROM phrase.

Subquery in SET of UPDATE

You can use query in SET phrase of UPDATE.

UPDATE sample54 SET a=(SELECT MAX(a) FROM sample54);

The example is not that useful but shows the possibility of command.

Subquery in FROM

As FROM phrase specifies table, it is okay not to use scalar subquery.

mysql> SELECT * FROM (SELECT * FROM sample54) sq;
+------+------+
| no   | a    |
+------+------+
|    1 |  100 |
|    2 |  900 |
|    4 |   80 |
+------+------+
3 rows in set (0.00 sec)

The structure of command is called ‘nested structure’, as SELECT command is in the SELECT command. sq is a nickname of table. Overlapping more than two subquery is also possible.

Subquery in INSERT

There are two types of using subquery in INSERT.

1. Subquery as a part of VALUES
   As VALUES set values to insert, subqueries in VALUES should be scalar subqueries.

    mysql> INSERT INTO sample541 VALUES(
    -> (SELECT COUNT(*) FROM sample51),
    -> (SELECT COUNT(*) FROM sample54)
    -> );
    Query OK, 1 row affected (0.00 sec)
   
    mysql> SELECT * FROM sample541;
    +------+------+
    | a    | b    |
    +------+------+
    |    5 |    3 |
    +------+------+
    1 row in set (0.00 sec)
   

2. INSERT SELECT
   Instead of VALUES phrase, you can use SELECT phrase.

    mysql> INSERT INTO sample541 SELECT 1, 5;
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
   
    mysql> SELECT * FROM sample541;
    +------+------+
    | a    | b    |
    +------+------+
    |    5 |    3 |
    |    1 |    5 |
    +------+------+
    2 rows in set (0.00 sec)
   

   The example above inserts 1, 5 which is returned by SELECT phrase. This INSERT SELECT is useful when you copy a table to another table.

    -- Copy sample543 to sample542
    INSERT INTO sample542 SELECT * FROM sample543;
   

All images, except those with separate source indications, are excerpted from lecture materials.