Take a practical example to understand the associated subquery
Left join: A left-hand connection that returns all the records in the left table and the records in the right table that are equal to the join field.
Right join: A right-hand join that returns all the records in the right table and the records in the left table that are equal to the join field.
INNER JOIN: An inner join, also called an equivalent connection, that returns only rows of two tables that have the same connection field.
Full join: Outer joins, returning rows from two tables: Left JOIN + RIGHT Join
Cross join: The result is a Cartesian product, which is the number of rows in the first table multiplied by the number of rows in the second table.
code is as follows |
copy code |
Declare @a Table (a int,b int) Declare @b table (a int,b int) Insert @a values (1,1) Insert @a values (2,2) Insert @b VALUES (1,1) Insert @b values (3,3) Select * FROM @a Select * to @b --Left: Select * FROM @a Aa-down Joi n @b Bb on AA.A=BB.A --Right: Select * from @a AA right-hand join @b Bb on AA.A=BB.A --Inside Select * from @a Aa inner Join @b Bb on AA.A=BB.A --Outside: Select * FROM @a Aa full join @b Bb on AA.A=BB.A --cross-connect Select * from @a ; Cross join @b |
Efficiency of associative subquery
It is obvious that in general, the efficiency of the associated subquery is relatively low, in fact, the associated subquery in this example is only to demonstrate the principle and usage of the associated subquery. If you can, the associated subquery tries to replace it with joins or other queries. In this example, the SQL replaced with the INNER JOIN is:
The code is as follows |
Copy Code |
SELECT article.* from article INNER JOIN user on article.uid = User.uid
|
Note: This is just to demonstrate that the INNER JOIN replaces the associated subquery, not the table name, which is the optimal processing