Detailed Explanation of MySQL Subquery Operation Instance

This article summarizes the MySQL subquery operation with examples. Share it for your reference, as follows:

Define two tables tb1 and tb2

CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);

Insert data into two tables:

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

any Subquery for some Keyword

SELECT num1
FROM tbl1
WHERE num1 > ANY (SELECT num2 FROM tbl2);

all Subquery of keyword

SELECT num1
FROM tbl1
WHERE num1 > ALL (SELECT num2 FROM tbl2);

exists Subquery of keyword

SELECT * from fruits
WHERE EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT * from fruits
WHERE f_price>10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT * from fruits
WHERE NOT EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

Belt in Subquery of keyword

SELECT c_id
FROM orders
WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

SELECT c_id
FROM orders
WHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

Subquery with comparison operator

SELECT s_id, f_name FROM fruits
WHERE s_id =
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

< > All non-

SELECT s_id, f_name FROM fruits
WHERE s_id <>
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

Define two tables tb1 and tb2

CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);

Insert data into two tables

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

"Example. 53" returns all num2 columns of the tbl2 table, and then compares the value of num1 in tbl1 with it, as long as any value greater than num2 is the result of the query criteria

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

0

"Example. 54" returns the value in the tbl1 table that is greater than all the values in the num2 column of the tbl2 table

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

1

[Example. 55] Query whether the vendor of s_id=107 exists in the table suppliers, and if so, query the record in the table fruits

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

2

[Example. 56] Query whether a supplier with s_id=107 exists in the table suppliers, and if so, query the record with f_price greater than 10.20 in the table fruits

SELECT * from fruits
WHERE f_price>10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

[Example. 57] Query whether the vendor of s_id=107 exists in the table suppliers, and if not, query the record in the table fruits

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

4

[Example. 58] Query the order number for which f_id is c0 in the orderitems table, and query the customer c_id with the order number according to the order number

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

5

"Example. 59" is similar to the previous example statement, but the NOT IN operator is used in the SELECT statement

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

6

[Example. 60] Query the supplier s_id with s_city equal to Tianjin in the suppliers table, and then query all kinds of fruits provided by this supplier in the fruits table

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

7

[Example. 61] Query the supplier s_id with s_city equal to Tianjin in the suppliers table, and then query the fruits table for all kinds of fruit not provided by the supplier. The SQL statement is as follows:

SELECT s_id, f_name FROM fruits
WHERE s_id <>
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

More readers interested in MySQL can check out the topics on this site: "MySQL Common Function Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"

I hope this article is helpful to everyone's MySQL database.