On Tuesday, keep learning!
Previously we learned the simple usage of the SELECT statement (a single statement that retrieves data from a single database table). Today we will learn a little more complicated usage.
Query (query) Any SQL statement is a query. But this term generally refers to the SELECT statement.
SQL also allows the creation of subquery (subquery), that is, queries nested in other queries .
Use subqueries to filter
We now have the following two tables. The first is student and the second is teacher. Now we want to find out who is the age of teacher Poul’s student? How to do it?
The first is to find the students of Teacher Poul,
and then find their age
. The IN statement we learned before is used above. The above is completed by two queries. We can also use one query.
In the SELECT statement, the subquery is always inward. Outside treatment.
Using subqueries in the WHERE clause can write powerful and flexible SQL statements. There is no limit to the number of subqueries that can be nested, but due to performance limitations in actual use, too many subqueries cannot be nested.
Subqueries are generally used in combination with the IN operator, but can also be used to test for equality (=), inequality (<>), etc.
Use subqueries as calculated fields
Earlier we learned about the calculation field, so today we will review, look at the following example, we want to get the number of students taught by each teacher, what should we do?
Return three columns, of which the third column is a calculated field. The subquery obtained by
the subquery uses the fully qualified column name (if there are multiple tables with the same column name, use this method), if you don’t need to return the result Something went wrong.
That's it for today~