2. A standard from clause containing one or more tables or view names.
3. Optional where clause.
4. Optional group by clause.
5. Optional having clause.
2. Syntax rules for subquery
1. The select query of subqueries is always enclosed in parentheses.
2. Compute or clause cannot be included.
3. If the top clause is specified at the same time, it may only include the order by clause.
4. Subqueries can be nested to up to 32 layers. Individual queries may not support 32-layer nesting.
5. Subquery can be used anywhere an expression can be used as long as it returns a single value.
6. If a table only appears in a subquery and two do not appear in an external query, then the columns of the table cannot be included in the output.
3. Return a subquery of a value
When the return value of the subquery is only one, you can use comparison operators such as =, <, >, >=, <=, !=, etc. to connect the rich query and the subquery. Examples are as follows:
3.1 Simple subquery
select name,age
from person
where age > ( select age
from person
where name = 'Zhang San');
3.2 Like nested query
select count(distinct(Tname)) --Query the number of teachers with the surname "Zhang"
from Teacher
where Tname like 'Zhang %';
4. Return a subquery of a set of values
If the subquery returns more than one value but is a collection, you cannot use the comparison operator directly. ANY, SOME, or ALL can be inserted between the comparison operator and the subquery. The IN operator can be used for the equal value relationship.
4.1 in Nested Query
The in keyword is used in the where clause to determine whether the expression of the query is in a list of multiple values. Returns a record that satisfies the conditions in the in list.
select name
from person
where countryid in ( select countryid
from country
where countryname = 'China');
4.2 Some nested query
Some logical operator symbols in SQL. If some values are True in a series of comparisons, the result is True. Some syntax is:
<Expression>{ =|<>|!=|>|>=|!>|<|<=|!<}some(subquery)
select name from person
where countryid = some ( select countryid from country--Compare with the equal sign with the value found below. If it is equal to one of them, it will return
where countryname = 'China');
4.3 all nested query
All is a logical operator in SQL. If a series of comparisons are true, then the result can be true.
<Expression>{ =|<>|!=|>|>=|!>|<|<=|!<}all(subquery)
select name from person
where countryid > all ( select countryid from country -- When countryid is greater than all ids returned below, this result is True, and this result will be returned
where countryname = 'China');
4.4 exists nested query
exists are logical operator symbols in SQL. If the subquery has a result set returned, then it is True. Exists stands for the meaning of "existence", and it only looks for those records that meet the criteria. Once the first matching record is found, stop searching immediately.
exists subquery
Among them, the subquery is a first select statement, and there are no compute clauses and into keywords allowed.
Exists means whether the subquery has a result set to return.
SELECT * FROM Person
WHERE exists ( SELECT 1); --SELECT 0 SELECT NULL The results are the same because all three subqueries have result sets to return, so it is always True, SELECT * FROM Person executes as usual
However, if the subquery does not return the result set because the condition is added, the subject statement will not be executed:
SELECT * FROM Person
WHERE exists ( SELECT * FROM Person
WHERE Person_Id = 100); --If there is no record of Person_Id, the subquery has no result set returned and the subject statement will not be executed
5. Return the subquery of the new data table
5.1 Query the student IDs of all students whose "001" courses have higher scores than "002" courses;
Select from (select Sno,score from SC where Sno='001') a,
(select Sno,scorefrom SC where Cno='002') b
Where > and =;
3. Optional where clause.
4. Optional group by clause.
5. Optional having clause.
2. Syntax rules for subquery
1. The select query of subqueries is always enclosed in parentheses.
2. Compute or clause cannot be included.
3. If the top clause is specified at the same time, it may only include the order by clause.
4. Subqueries can be nested to up to 32 layers. Individual queries may not support 32-layer nesting.
5. Subquery can be used anywhere an expression can be used as long as it returns a single value.
6. If a table only appears in a subquery and two do not appear in an external query, then the columns of the table cannot be included in the output.
3. Return a subquery of a value
When the return value of the subquery is only one, you can use comparison operators such as =, <, >, >=, <=, !=, etc. to connect the rich query and the subquery. Examples are as follows:
3.1 Simple subquery
select name,age
from person
where age > ( select age
from person
where name = 'Zhang San');
3.2 Like nested query
select count(distinct(Tname)) --Query the number of teachers with the surname "Zhang"
from Teacher
where Tname like 'Zhang %';
4. Return a subquery of a set of values
If the subquery returns more than one value but is a collection, you cannot use the comparison operator directly. ANY, SOME, or ALL can be inserted between the comparison operator and the subquery. The IN operator can be used for the equal value relationship.
4.1 in Nested Query
The in keyword is used in the where clause to determine whether the expression of the query is in a list of multiple values. Returns a record that satisfies the conditions in the in list.
select name
from person
where countryid in ( select countryid
from country
where countryname = 'China');
4.2 Some nested query
Some logical operator symbols in SQL. If some values are True in a series of comparisons, the result is True. Some syntax is:
<Expression>{ =|<>|!=|>|>=|!>|<|<=|!<}some(subquery)
select name from person
where countryid = some ( select countryid from country--Compare with the equal sign with the value found below. If it is equal to one of them, it will return
where countryname = 'China');
4.3 all nested query
All is a logical operator in SQL. If a series of comparisons are true, then the result can be true.
<Expression>{ =|<>|!=|>|>=|!>|<|<=|!<}all(subquery)
select name from person
where countryid > all ( select countryid from country -- When countryid is greater than all ids returned below, this result is True, and this result will be returned
where countryname = 'China');
4.4 exists nested query
exists are logical operator symbols in SQL. If the subquery has a result set returned, then it is True. Exists stands for the meaning of "existence", and it only looks for those records that meet the criteria. Once the first matching record is found, stop searching immediately.
exists subquery
Among them, the subquery is a first select statement, and there are no compute clauses and into keywords allowed.
Exists means whether the subquery has a result set to return.
SELECT * FROM Person
WHERE exists ( SELECT 1); --SELECT 0 SELECT NULL The results are the same because all three subqueries have result sets to return, so it is always True, SELECT * FROM Person executes as usual
However, if the subquery does not return the result set because the condition is added, the subject statement will not be executed:
SELECT * FROM Person
WHERE exists ( SELECT * FROM Person
WHERE Person_Id = 100); --If there is no record of Person_Id, the subquery has no result set returned and the subject statement will not be executed
5. Return the subquery of the new data table
5.1 Query the student IDs of all students whose "001" courses have higher scores than "002" courses;
Select from (select Sno,score from SC where Sno='001') a,
(select Sno,scorefrom SC where Cno='002') b
Where > and =;