Shulou(Shulou.com)06/01 Report--

This article summarizes MySQL subquery operations 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);

Subqueries of any some keywords

SELECT num1FROM tbl1WHERE num1 > ANY (SELECT num2 FROM tbl2);

Subqueries for all keywords

SELECT num1FROM tbl1WHERE num1 > ALL (SELECT num2 FROM tbl2);

Subqueries for the exists keyword

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

Subqueries with in keyword

SELECT c_idFROM ordersWHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');SELECT c_idFROM ordersWHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

Subqueries with Comparison Operators

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

all non

SELECT s_id, f_name FROM fruitsWHERE 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);

Inserting data into two tables

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

Return all num2 columns of tbl2 table, and then compare the value of num1 in tbl1 with it, as long as any value greater than num2 is the result that meets the query condition.

SELECT num1FROM tbl1WHERE num1 > ANY (SELECT num2 FROM tbl2);

[Example 54] Returns the value in the tbl1 table that is greater than all the values in the tbl2 table num2 column

SELECT num1FROM tbl1WHERE num1 > ALL (SELECT num2 FROM tbl2);

[Example 55] Query whether there is a supplier with s_id=107 in the suppliers table. If there is, query the records in the fruits table.

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

[Example 56] Query whether there is a supplier with s_id=107 in the suppliers table. If there is, query the records with f_price greater than 10.20 in the fruits table.

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

[Example 57] Query whether there is a supplier with s_id=107 in the suppliers table. If there is no supplier, query the records in the fruits table.

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

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

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

Similar to the previous example statement, but using the NOT IN operator in the SELECT statement

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

[Example 60] Query the supplier s_id whose s_city is equal to Tianjin in the suppliers table, and then query all the fruit types provided by this supplier in the fruits table.

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

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

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

More about MySQL related content interested readers can view this site topic: "MySQL common function summary","MySQL log operation skills","MySQL transaction operation skills summary","MySQL storage process skills" and "MySQL database lock related skills summary"

I hope this article is helpful for MySQL database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.