Shulou(Shulou.com)05/31 Report--

Mysql in how to implement sub-query and joint query, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

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 that takes the result of the inner query as the condition of the outer query.

From subquery

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

Difference:

For columns that are not the only values, using where subcheck may produce incorrect results. If you use from, if there is a grouping, we need to sort the required records in the first place.

Exists

# query the columns with goods

The code copies the code select cat_id,cat_name from category where cat_id as follows

In

(select distinct cat_id from goods)

We can also use the Exists subquery:

The code copies the code select cat_id,cat_name from category where exists as follows

(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, while the queries subquery is queried multiple times (as many rows as there are records).

SQL UNION operator

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

Note that SELECT statements within UNION must have the same number of columns. Columns must also have similar data types. At the same time, the columns in each SELECT statement must be in the same order.

SQL UNION syntax

The code copies the code as follows

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, UNION is de-duplicated. If duplicate values are allowed, use UNION ALL.

SQL UNION ALL syntax

The code copies the code as follows

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 equal to the column name in the first SELECT statement in UNION.

The purpose of the UNION directive is to merge the results of two SQL statements. From this point of view, UNION is somewhat similar to JOIN because both instructions can retrieve data from multiple tables. Union only joins two results together to display, not join two tables

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.