How to use Mysql subquery keywords

1. all

1.1 Format

1.2 Features

all: If compared with all values ​​returned by the subquery, it is true. Return true

all can be used in combination with =,>=,>,, which respectively means equal to, greater than or equal to, greater than, less than, less than or equal to, not equal to All the data

is greater than all, which means that the value in the specified column must be greater than every value in the subquery set, that is, it must be greater than the maximum value of the subquery set; if it is less than, it is less than the minimum value in the subquery set. value.

1.3 Operation

2. any (some)

1.1 Format

1.2 Features

• any: If compared with all values ​​returned by the subquery, true will be returned

• ##any can be used with =,> =,>, are used in combination to represent equal to, greater than or equal to, greater than, less than, less than or equal to, and not equal to any of the data

• Greater than any means that the value in the specified column must be greater than any value in the subquery set, that is, it must be greater than the minimum value of the subquery set; the same is true for others

• some can understand Alias ​​for any

1.3 Operation

##3. in

1.1 Format

1.2 Features

in is used to determine whether the value of a record is in the specified collection
Add not in front of the in keyword to reverse the condition
1.3 Operation

4. exist

1.1 Format

1.2 Features

If this subquery "has data results" (returns at least one row of data), then The EXISTS() result is: true, and the outer query is executed
If the subquery has "no data result" (no data is returned), the EXISTS() result is: false , the outer query is not executed
The subquery after EXISTS does not return any actual data, only true or false, and when true is returned, the where condition is established
Note: The EXISTS keyword is more efficient than the IN keyword. Therefore, in actual development, especially when the amount of data is large, it is recommended to use the EXISTS keyword
1.3 Operation

1.4 Explanation

When the outer query is queried, the outer query will be executed row by row. When the first row is executed, it will be judged whether the subquery is If it is true, this row of data is found, and then the second row of data is queried. . . Repeat this.

The above is the detailed content of How to use Mysql subquery keywords. For more information, please follow other related articles on the PHP Chinese website!