11. subquery

In SQL statements, subqueries must be placed in parentheses. When executing a subquery, The subquery results are first queried, then, the results returned by the subquery are used as the query conditions for the outer query. Subqueries can be divided into single-row subqueries, multi-row subqueries, and multi-column subqueries Based on the returned data and the relationship between outer queries, it can be divided into related subqueries and irrelevant subqueries. It should be said that subqueries are widely used in the actual development process.

Single Row subquery

In practical applications, if developers or users explicitly know that the subquery used in their SQL statements returns a row of data, you can use arithmetic comparison operators to perform subqueries. The comparison operators include = (equal to),> = (greater than or equal to), <= (less than or equal to),> (greater than), <(less than), and ),! = (Not equal to), <> (not equal to),!> (Not greater ),! <(Not less ).

Multi-row subquery

Multi-row subquery indicates that the results returned by the subquery contain multiple rows of data. When a subquery returns multi-row data, you must use the multi-row operator. Multi-line operators include other operators.
Using the IN operator, you can query any value that meets the specified expression IN the list. When the IN operator is used IN a subquery, the results that match any value IN the result set IN the subquery will be queried.
The ANY operator is also used in multi-row subqueries. The meaning of the ANY operator is that as long as it matches ANY result value in the subquery, its value will be returned. The ANY operator must be used with comparison operators (= (equal to),> = (greater than or equal to), <= (less than or equal to),> (greater than), <(less than), and ),! = (Not equal to), <> (not equal to) together. SOME is a synonym of ANY.
Multi-column subquery

Multi-column subquery is a subquery statement that returns multiple data columns. In the WHERE clause, you can also use parentheses to enclose multiple attribute values for multi-column subqueries. In multi-column subqueries, the WHERE clause must use parentheses to enclose multiple attributes. Multiple Attributes must be separated by commas.
In the WHERE clause of an outer query, different operators can be used based on the number of rows returned by multi-column subqueries. If the data rows returned by a multi-column subquery are single rows, that is, the returned result value is only one, you can use the arithmetic comparison operator. If the data rows returned by the subquery are multiple rows, if more than one result value is returned, the IN, ANY, and ALL operators can be used.

 (dept,profession)  teaID  )

Related subqueries

In the SQL statement subquery described earlier, the subquery statements are executed first, and then the results returned by the subquery are used as the query conditions for the outer query to retrieve data. The subquery is executed only once. The subquery related to the primary query must reference the values in the primary query. Therefore, the subquery must depend on the primary query, subqueries related to the primary query cannot be executed independently from the primary query as an independent query command.

 j  t2  j  ( i  t1);  j  t2   ( i  t1  i  j); 

That is to say, the related subquery must be dependent on the outer query, and the outer query and subquery must be related. Subqueries related to the primary query work in the following way: Pass the value from the primary query to the subquery to see if they meet the conditions given in the Self-query. Generally, the EXISTS keyword or not exists keyword is used to implement related subqueries. When executing a subquery with the EXISTS keyword, only the logical value TRUE or TALSE is returned, and no data is returned. That is to say, subqueries with the EXISTS keyword do not care about the returned data, but only care about the returned data "yes or no ". (Test whether a subquery returns data rows ).
The query method for related subqueries using the not exists keyword is the opposite of that for related subqueries using the EXISTS keyword. If no results are returned in the subquery of the not exists clause, the WHERE clause of the outer query returns TRUE, and the result set of the final query is displayed; if the subquery of the not exists clause has returned results, the WHERE clause of the outer query returns FALSE.

Use subqueries in SQL statements

In addition to the WHERE clause, it can also be applied to SELECT clauses, FROM clauses, order by clauses, HAVING clauses, create table statements, create view statements, INSERT statements, UPDATE statements, and DELETE statements.
Use the subquery in the select clause to query the score information of the student number s102203.

 stuID   R.stuID)  R.stuID    R.result 

Use a subquery in the from clause to create a temporary data table. Query the course selection score of student s102203

 R.curID R.stuID    R.result 

You can use a subquery in the having clause to create a temporary data table. Query the average scores of students starting with student ID s2.

 R.stuID,  R.curID  R.stuID  stuID    R.stuID

Multiple subqueries allow multiple subquery statements in the query conditions. Query the information about teachers whose titles are the same as those of the teachers whose number is t181585 but whose salaries are higher than the teachers.

 profession  teaID   teaID  )

Use subqueries In the create table statement to replicate data tables, by using subqueries In the create table statement, you can CREATE a new data TABLE and insert the data in the original TABLE into the new data TABLE. This allows you to copy data in the data TABLE. The syntax format is as follows: