MySQL multi-table Query sub-query

Any and SOME Subqueries

The any and some keywords are synonyms that indicate that any of these conditions are met.

By creating an expression, you compare the returned results and output the results that match the criteria.

Mysql> select * from T2;

+------+-------+------+

| ID | name | Age |

+------+-------+------+

| 1 |   Mark | 29 |

| 2 |   Frank | 32 |

| 3 |   Niko | 27 |

+------+-------+------+

3 Rows in Set (0.00 sec)

Mysql> select * from T1;

+------+-------+------+

| ID | name | Age |

+------+-------+------+

| 1 |   Tube | 31 |

| 2 |   Kevin | 34 |

| 3 |   Todd | 32 |

+------+-------+------+

3 Rows in Set (0.00 sec)

Mysql> select * FROM T2 where-age >any (select-age from T1);

+------+-------+------+

| ID | name | Age |

+------+-------+------+

| 2 |   Frank | 32 |

+------+-------+------+

1 row in Set (0.07 sec)

EXITS and not EXITS subqueries

EXITS and not EXITS can be said to be a judgment subquery.

The system queries the subquery and determines if there is a return row. If any, the query in the outer statement is executed, and if not, the query is not made.

Mysql> select * from log;

+------+----------+------------------------------------------------+

| ID | Category | Log |

+------+----------+------------------------------------------------+

| 1 | Nginx | Upstream timed out (110:connection timed out) |

| 2 | Apache | File does notexist:/var/www/html/1.html |

| 3 | Apache | Could not Openfile:/var/images/banner.gif |

| 4 | MySQL | Innodb:unable tolock/ibdata1, Error:11 |

+------+----------+------------------------------------------------+

4 rows in Set (0.05 sec)

Mysql>

Mysql>

Mysql> SELECT * from state;

+---------+-------+---------+---------+

| Apache | Nginx | App | MySQL |

+---------+-------+---------+---------+

| Succeed | Fail | Succeed | Succeed |

+---------+-------+---------+---------+

1 row in Set (0.09 sec)

Mysql> select * from log where category= ' Nginx ' and exists (SELECT * from state where nginx= ' Fail ');

+------+----------+------------------------------------------------+

| ID | Category | Log |

+------+----------+------------------------------------------------+

| 1 | Nginx | Upstream timed out (110:connection timed out) |

+------+----------+------------------------------------------------+

1 row in Set (0.00 sec)

If the result of the query is true, the display is false.

Mysql> select * from log where category= ' Apache ';

+------+----------+--------------------------------------------+

| ID | Category | Log |

+------+----------+--------------------------------------------+

| 2 | Apache | File does notexist:/var/www/html/1.html |

| 3 | Apache | Could not Openfile:/var/images/banner.gif |

+------+----------+--------------------------------------------+

2 rows in Set (0.00 sec)

Mysql> select * from log where category= ' Apache ' and exists (SELECT * from state where apache= ' Fail ');

Empty Set (0.00 sec)

In sub-query

The in keyword evaluates the subquery result and determines whether the row is returned. Executes an external query statement if one is available. If not, it is not executed.

The results returned by the out-of-query match the results of the subquery.

Mysql> SELECT * from people;

+------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+------+------+-----------+-------------+

|   Lee | 20 | 123456789 | 13021981234 |

|  John | 32 | 987654321 | 13998763456 |

|  Lucy | 45 | 678954321 | 13098784321 |

|   Ken | 33 | 986745321 | 18609073544 |

+------+------+-----------+-------------+

4 rows in Set (0.03 sec)

Mysql> select * from blacklist;

+------+------+-------+--------+

| ID | Name | Price | Level |

+------+------+-------+--------+

| 1 |  John | 3000 | normal |

| 2 | Lucy | 24000 | Hard |

+------+------+-------+--------+

2 rows in Set (0.08 sec)

Mysql> SELECT * from people where Namein

(select Name from blacklist);

+------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+------+------+-----------+-------------+

|  John | 32 | 987654321 | 13998763456 |

|  Lucy | 45 | 678954321 | 13098784321 |

+------+------+-----------+-------------+

2 rows in Set (0.00 sec)

The in subquery is suitable for picking small result sets from a large result set that specify criteria. This small result set is also the content of another table.

Union subquery (uniqueness)

Union is used to merge query results. You can combine the results of multiple SELECT statement queries into a single result set.

the number of columns must be the same, using The ALL keyword is spliced into two tables.

Mysql> SELECT * from people;

+------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+------+------+-----------+-------------+

|   Lee | 20 | 123456789 | 13021981234 |

|  John | 32 | 987654321 | 13998763456 |

|  Lucy | 45 | 678954321 | 13098784321 |

|   Ken | 33 | 986745321 | 18609073544 |

+------+------+-----------+-------------+

4 rows in Set (0.00 sec)

Mysql> CREATE table People2 like people;

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO People2 select * from people where age=45;

Query OK, 1 row affected (0.02 sec)

Records:1 duplicates:0 warnings:0

Mysql> select * from People2;

+------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+------+------+-----------+-------------+

|  Lucy | 45 | 678954321 | 13098784321 |

+------+------+-----------+-------------+

1 row in Set (0.00 sec)

mysql> INSERT into People2 values (' Jingjing ', 31,123321456,18812344321);

Query OK, 1 row affected (0.01 sec)

Mysql> SELECT * from people;

+------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+------+------+-----------+-------------+

|   Lee | 20 | 123456789 | 13021981234 |

|  John | 32 | 987654321 | 13998763456 |

|  Lucy | 45 | 678954321 | 13098784321 |

|   Ken | 33 | 986745321 | 18609073544 |

+------+------+-----------+-------------+

4 rows in Set (0.00 sec)

Mysql> select * from People2;

+----------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+----------+------+-----------+-------------+

|   Lucy | 45 | 678954321 | 13098784321 |

|   Jingjing | 31 | 123321456 | 18812344321 |

+----------+------+-----------+-------------+

2 rows in Set (0.00 sec)

Mysql> SELECT * from people union select* from People2;

+----------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+----------+------+-----------+-------------+

|   Lee | 20 | 123456789 | 13021981234 |

|   John | 32 | 987654321 | 13998763456 |

|   Lucy | 45 | 678954321 | 13098784321 |

|   Ken | 33 | 986745321 | 18609073544 |

|   Jingjing | 31 | 123321456 | 18812344321 |

+----------+------+-----------+-------------+

5 rows in Set (0.00 sec)

A repeating piece of data does not exist.

Mysql> SELECT * FROM people Union allselect * from People2;

+----------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+----------+------+-----------+-------------+

|   Lee | 20 | 123456789 | 13021981234 |

|   John | 32 | 987654321 | 13998763456 |

|   Lucy | 45 | 678954321 | 13098784321 |

|   Ken | 33 | 986745321 | 18609073544 |

|   Lucy | 45 | 678954321 | 13098784321 |

|   Jingjing | 31 | 123321456 | 18812344321 |

+----------+------+-----------+-------------+

6 rows in Set (0.00 sec)

Simple stitching

Mysql> select * from blacklist;

+------+------+-------+--------+

| ID | Name | Price | Level |

+------+------+-------+--------+

| 1 |  John | 3000 | normal |

| 2 | Lucy | 24000 | Hard |

+------+------+-------+--------+

2 rows in Set (0.00 sec)

Mysql> select * from People2;

+----------+------+-----------+-------------+

| name | Age | Certnum | Phone |

+----------+------+-----------+-------------+

|   Lucy | 45 | 678954321 | 13098784321 |

|   Jingjing | 31 | 123321456 | 18812344321 |

+----------+------+-----------+-------------+

2 rows in Set (0.00 sec)

When the column data type is different

Mysql> select * FROM blacklist unionselect * from People2;

+----------+------+-----------+-------------+

| ID | Name | Price | Level |

+----------+------+-----------+-------------+

| 1 |      John | 3000 | normal |

| 2 |     Lucy | 24000 | Hard |

| Lucy | 45 | 678954321 | 13098784321 |

| Jingjing | 31 | 123321456 | 18812344321 |

+----------+------+-----------+-------------+

4 rows in Set (0.00 sec)

Mysql> SELECT * FROM blacklist Union Allselect * from People2;

+----------+------+-----------+-------------+

| ID | Name | Price | Level |

+----------+------+-----------+-------------+

| 1 |      John | 3000 | normal |

| 2 |     Lucy | 24000 | Hard |

| Lucy | 45 | 678954321 | 13098784321 |

| Jingjing | 31 | 123321456 | 18812344321 |

+----------+------+-----------+-------------+

4 rows in Set (0.00 sec)

Add or don't add All the same

Mysql> Select Name,price from blacklistunion all select Name,certnum from People2;

+----------+-----------+

| name | Price |

+----------+-----------+

|      John | 3000 |

|     Lucy | 24000 |

| Lucy | 678954321 |

| Jingjing | 123321456 |

+----------+-----------+

4 rows in Set (0.00 sec)

Mysql> Select Name,price from Blacklistunion select Name,certnum from People2;

+----------+-----------+

| name | Price |

+----------+-----------+

|      John | 3000 |

|     Lucy | 24000 |

| Lucy | 678954321 |

| Jingjing | 123321456 |

+----------+-----------+

4 rows in Set (0.00 sec)

mysql> Update People2 set certnum=24000where name= ' Lucy ';

Query OK, 1 row affected (0.02 sec)

Rows matched:1 changed:1 warnings:0

Mysql> Select Name,certnum from People2where name= ' Lucy ';

+------+---------+

| name | Certnum |

+------+---------+

|  Lucy | 24000 |

+------+---------+

1 row in Set (0.00 sec)

Mysql> Select Name,price from Blacklistunion select Name,certnum from People2;

+----------+-----------+

| name | Price |

+----------+-----------+

|      John | 3000 |

|     Lucy | 24000 |

| Jingjing | 123321456 |

+----------+-----------+

3 Rows in Set (0.00 sec)

Mysql> Select Name,price from blacklistunion all select Name,certnum from People2;

+----------+-----------+

| name | Price |

+----------+-----------+

|      John | 3000 |

|     Lucy | 24000 |

|     Lucy | 24000 |

| Jingjing | 123321456 |

+----------+-----------+

4 rows in Set (0.00 sec)

can you go heavy: It depends on the column data type, as well as data.

This article from "The Sun Snail" blog, declined reprint!

MySQL multi-table Query sub-query