Mysql subquery and UNION Query

In mysql, UNION queries can be combined with multiple tables. union is only a small point of the Combined Query in mysql. Next I will introduce various statements for the combined query in mysql, for more information, see.

Query:
In the select query, another select query is nested. One is the outer query, and the other is the inner query.
Where subquery

There is a select statement in the where query statement. The results of the inner layer query are used as conditions for the outer layer query.

From subquery

In the from query statement, there is a select statement that uses the inner query result as a temporary table for the outer layer to query again.

Differences:

For columns that are not unique values, incorrect results may occur when you use the where subquery. If the from clause is used and there are groups, we need to sort the records to the first position.

Exists

# Query the product Columns

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:

It is different from the where and from subqueries described earlier. The where and from subqueries are executed only once, the exists subquery needs to be queried multiple times (the number of rows of records is counted as many times ).

SQL UNION operator

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

Note that the SELECT statement inside the UNION must have the same number of columns. Columns must also have similar data types. In addition, the columns in each SELECT statement must be in the same order.

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 selects different values, that is, UNION is de-duplicated. If repeated 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 name in the UNION result set is always the same as the column name in the first SELECT statement in the UNION.

The purpose of the UNION command is to combine the results of two SQL statements. From this perspective, UNION and JOIN are somewhat similar, because both commands can retrieve data from multiple tables. Union only Concatenates the two results and displays them together. It does not join two tables.