MySQL subquery and Union (Union) query detailed

Inquire:
In a select query, another select query is nested. One is the outer query and the other is the inner query.
where child query

That is, the WHERE query statement has a SELECT statement that treats the results of the inner query as the condition of the outer query.

From child query

In the FROM query statement, there is a SELECT statement that takes the inner query result as a temporary table for the outer layer to query again.

Difference:

For columns that are not unique values, using the WHERE subquery may result in incorrect results. If you use from, and there are groupings, we need to sort the records in the first place.

Exists

#查询有商品的栏目

The code is as follows	Copy Code
Select Cat_id,cat_name from category where cat_id In (select distinct cat_id from goods);

We can also use the EXISTS subquery:

The code is as follows	Copy Code
Select Cat_id,cat_name from category where exists (SELECT * from goods where goods.cat_id = category.cat_id);

Execution process:

Unlike the where and from subqueries we talked about earlier, the where and from subqueries are executed only once, and the exists subquery is queried multiple times (how many rows of records are executed).

SQL UNION operator

The UNION operator is used to merge the result sets of two or more SELECT statements.

Note that the SELECT statement within the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.

SQL UNION Syntax

The code is as follows	Copy Code
SELECT column_name (s) from table_name1 UNION SELECT column_name (s) from table_name2

Note: By default, the union operator chooses a different value, that is, the union is gone heavy. If duplicate values are allowed, use UNION all.

SQL UNION All syntax

The code is as follows	Copy Code
SELECT column_name (s) from table_name1 UNION All SELECT column_name (s) from table_name2

In addition, the column names in the union result set are always equal to the column names in the first SELECT statement in the Union.

The purpose of the UNION directive is to combine the results of two SQL statements. From this point of view, the UNION is somewhat similar to the JOIN, since these two instructions can be retrieved from multiple tables. The union simply joins the two results together to show that it is not linked to two tables.