MySQL Knowledge Learning-Day Fourteen: Using Subqueries


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?

Insert picture description here
Insert picture description here

The first is to find the students of Teacher Poul,

Insert picture description here

and then find their age

Insert picture description here

. The IN statement we learned before is used above. The above is completed by two queries. We can also use one query.

Insert picture description here

In the SELECT statement, the subquery is always inward. Outside treatment.

Insert picture description here

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.

Insert picture description here

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?

Insert picture description here

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~