What should be the idea of subquerying multi table operations?

The solution steps of multi-table subqueries can be summarized as "primary query first, subqueries later", and refinement is the following three steps:

1) Distinguish the connection conditions between the main table and the sub-table + the two tables [the table placed in parentheses is the main table, and the table outside the parentheses is the sub-table].

2) Write out the main query select * from the main table where join the field in () parentheses are the placeholders of the subquery].

3) Write out the subquery in parentheses of the main query (the key point, get the goal of the subquery: replace the result field of the question with the connection field).

Single-table operations and multi-table operations have completely different solution ideas, so when we get a query problem, when we decide to use sub-query, we must first clarify whether the problem is a single-table operation or a multi-table operation. Whether the nucleator query is a multi-table operation or a single-table operation. Here's how to tell the difference:

Look at the fields and fields sought by the results'of'The preceding word (field) is not from a table.

For example, you can find information about the oldest student among the boys. Suppose there is a student table that records student information with gender and age, then this is a single table operation.

For example, query the course results of data source tracking database and system test. The course grades belong to the grade table, while the database and system tests belong to the hail curriculum, which is two tables, and the multi-table exercise is rushed. I talked about this in the dark horse programmer public class.

The main query and subqueries are all from a table, which is a single-table operation.

The main query and subqueries come from different tables, which is a multi-table operation.

The way to distinguish between is simple, which is to look at the field and field sought in the result'of'The preceding word (field) is not derived from a table Bucha.

If the same table is used, it is a single-table operation.

If it comes from different tables, it is a multi-table operation.

The solution steps of multi-table sub-query are relatively simple, which can be summarized as "sub-query first, then main query", and refinement is the following three steps:

1) Inquire first.

2) After the main query.

3) Bond.

At present, the basic class of software testing for dark horse programmers is free, which is very suitable for those who want to learn software testing.

It can be written like this:

select (select rname from t reader where rid= reader's name, select bname from t book where bid= as book name, penalty date, penalty amount.

from t_penalty as p

But queries are much less efficient than connections.

select (select rname from t reader where rid= reader's name, select bname from t book where bid= as book name, penalty date, penalty amount.

from t_penalty as p

Subqueries and multi-table queries can generally be converted to each other.

In the case of both, subqueries are generally selected to meet the requirements.

Hibernate needs to work out the relationships between entities.

mybaits are for people who are not good at writing SQL.

Annotations are a framework mechanism that allows you to write fewer configuration files.

Yes, that's where subqueries come in.

Subqueries are about the same as associations, but it depends on how you write them.

Written like lz, it should be fast for correlation queries.

Try to avoid using in and not in in queries

If you don't use a subquery.

You can write it like this.

select *

from t_user a

where exists(select 1 from t_id b where =

In the SQL language, when a query statement is nested within the query conditions of another query, it is called a subquery. Subqueries are always written in parentheses and can be used anywhere the expression is used. Such as:

Queries nested within a select, insert, update, or delete statement or other subquery. Subqueries typically return only one result, while multi-table joins can return result sets.

Subquery optimization strategy.

For different types of subqueries, the optimizer chooses a different strategy.

1.For the in, =any subquery, the optimizer has the following policy options:

semijoin

materialization

exists

2.For the not in and <>all subqueries, the optimizer has the following policy options:

materialization

exists

3.For derived tables, the optimizer has the following policy options:

derived merge, which merges the derived table into an external query ( Introduced );

Materialize derived tables into internal temporary tables and then use them for external queries.

Note: Subqueries in the update and delete statements cannot use the semijoin or materialization optimization strategies.