The usage of MYSQL subquery and merge query

Subquery subquery with IN keyword

The comparison operator applies only if the result column returned by the subquery contains a value. If the result set returned by a subquery is a list of values, the comparison operator must be replaced by the IN operator.

The IN operator can detect whether a specific value exists in the result set and execute an external query if the test is successful.

Example 1:

View the score information corresponding to the qualified contents of the score field in the info table

SELECT * from info where score in (SELECT score FROM infos)

Query results:

Example 2:

View the same celebrity information in infos table and info table

SELECT * from info where name in (SELECT name FROM infos)

Query results:

Subquery with comparison operator

Subqueries can use comparison operators. These comparison operators include =,! =, >, > =, ANY (SELECT age from infos)

Query results:

Subquery with ALL keyword

The ALL keyword indicates that all conditions are met. When using the ALL keyword, the outer query statement can be executed only if all the results returned by the inner query statement are satisfied.

Example:

SELECT * from info where age < all (SELECT age from infos)

Query results:

Merge query

To merge query results is to merge the query results of multiple SELECT statements together. Merge query results using the UNION and UNION ALL keywords.

Example 1:

SELECT score from info UNION SELECT score from infos

Query results:

Example 2:

SELECT score from info UNION SELECT name from infos

Query results: