Go to SQL Union and SQL UNION ALL usage difference efficiency and issues with order by and group by mates

SQL Union and SQL UNION ALL usage difference efficiency and the problem with order by and group by mates

SQL Union and SQL UNION ALL usage

SQL UNION operator

The UNION operator is used to combine the result set of two or more SELECT statements.

Note that the SELECT statement inside 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
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 going to be heavy. If duplicate values are allowed, use UNION all.

SQL UNION All syntax
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 the Union.

The purpose of the UNION directive is to combine the results of two SQL statements. From this point of view,UNION is somewhat similar to join, because these two instructions can be retrieved from multiple tables. Union only shows the two results together, not the two tables ........

The syntax of UNION is as follows:

[SQL statement 1]
UNION
[SQL Statement 2] Let's say we have the following two tables,

store_information table jan-08-1999 Td> store_name sales date los Angeles $1500 jan-05-1999 san Diego $250 jan-07-19 los Angeles $300 boston $700 jan-08-1999

Internet Sales table Date Sales jan-07-1999 $ jan-10-1999 $535 jan-11-1999 $320 jan-12-1999 $750

And we want to find out all the days that have turnover (sales). To achieve this, we use the following SQL statement: SELECT Date from Store_information
UNION
SELECT Date from Internet_sales results:

date

jan-05-1999

jan-07-1999

jan-08-1999

jan-10-1999

jan-11-1999

jan-12-1999

SELECT DISTINCT Date " in any SQL statement (or both), we'll get exactly the same result.

SQL UNION ALL
UNION ALL The purpose of this directive is to combine the results of two SQL statements. UNION ALL differs from union in that the union ALL lists each qualifying material, regardless of whether the data value is duplicated or not. The syntax for UNION all is as follows: [SQL statement 1]
UNION all
[SQL Statement 2] we use the same example as the previous page to show the UNION All and UNION are different. Also assume that we have the following two tables,

store_information table jan-08-1999 Td> store_name sales date los Angeles $1500 jan-05-1999 san Diego $250 jan-07-19 los Angeles $300 boston $700 jan-08-1999

Internet Sales table Date Sales jan-07-1999 $ jan-10-1999 $535 jan-11-1999 $320 jan-12-1999 $750

And we need to find out the days of store turnover and network turnover. To achieve this, we use the following SQL statement: SELECT Date fromstore_information
UNION All
SELECT Date from Internet_sales results:

Date

jan-05-1999

jan-07-1999

jan-08-1999

jan-08-1999

jan-07-1999

jan-10-1999

jan-11-1999

jan-12-1999

See also:

The union in Oracle differs from UNION ALL

=============================================================================================================== =====================

SQL UNION ALL performs much more efficiently than SQL Union.

In the database, the Union and UNION ALL keywords combine two result sets into one, but they differ in both usage and efficiency.
The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. most of the actual applications do not produce duplicate records, the most common being the process table and the History table Union. Such as:
SELECT * FROM Gc_dfys
Union
SELECT * FROM Ls_jg_dfys
This SQL takes out the results of two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may cause the disk to be sorted if the table data volume is large.
The union all simply merges two results and returns. Thus, if there are duplicate data in the two result sets returned, the returned result set will contain duplicate data.
In terms of efficiency, union All is much faster than union, so if you can confirm that the combined two result sets do not contain duplicate data, then use Union ALL, as follows:
SELECT * FROM Gc_dfys
UNION ALL
SELECT * FROM Ls_jg_dfys

Comments:

1. It is worth noting that SQL union all performs much more efficiently than SQL Union because it is important that you use SQL Union for the purposes of draining, and that SQL union all does not need to be drained. Because for some queries that simply use a table to improve efficiency, you can use SQL UNION ALL.
Also, if a union is used, either SQL Union or SQL UNION all must remember to index the database table!

2,Union after the table link will filter out duplicate records, so on the table after the resulting result set will be sorted operations, delete duplicate records and return the results.

That is, the process of filtering out duplicate records is to sort The resulting set of results (all the columns on the order by table) after the tables are connected. It is then deleted based on whether the next row of the table is the same as the previous row to determine whether the next line is a duplicate row.

Reference:

http://blog.163.com/fly_sky_java/blog/static/14042223420106895310701/

Baidu UNION ALL usage

=============================================================================================================== ===================

UNION All and order by use problems

SELECT * FROM (SELECT-Zxbz,count (*) RS from dc_jhmy where 1=1 Group by ZXBZ ORDER BY Rs Desc
UNION ALL
Select ' HJ ' as Zxbz,count (*) RS from dc_jhmy where 1=1) where rownum <= 100

SQL statement above, I want to implement all records except the last union all sorted first, then add the last record, but order by Rs Desc
Place an error in the current position, the command did not end correctly.

The workaround is as follows: using temporary tables

SELECT * FROM (select Zxbz,count (*) RS to dc_jhmy where 1=1 Group by ZXBZ ORDER BY Rs Desc)
UNION ALL
Select ' HJ ' as Zxbz,count (*) RS from dc_jhmy where 1=1) where rownum <= 100

Reference:

Http://blog.sina.com.cn/s/blog_6cb0deff0100t4l8.html

Baidu UNION ALL ORDER BY

=============================================================================================================== =====================

Order problems for union All and GROUP by

2013-08-27 13:13:28 I'd say two sources: Sarah CLA's column.

The Order of the Union All and group by three experiments to see the effect of the different use order of union All and group by on the CPU time and elapsed time 1. First UNION all, then group by?

1234567891011

select inctmid ctmid,cnlid,inwhsid whsid,sum(qty) qty,goodsidfrom(     selectinctmid,cnlid,inwhsid,qty,goodsid       fromDtrBill a,DtrDetail b       wherea.billno=b.billno       unionall       selectinctmid,cnlid,inwhsid,-1*qty qty,goodsid       fromDtrBillRet a,DtrDetailRet b       wherea.billno=b.billno)agroup by inctmid,cnlid,inwhsid,goodsid

2. Group BY, then union all, then group by?

12345678910111213

select inctmid ctmid,cnlid,inwhsid whsid,sum(qty) qty,goodsidfrom(    selectinctmid,cnlid,inwhsid,sum(qty) qty,goodsid      fromDtrBill a,DtrDetail b      wherea.billno=b.billno      groupbyinctmid,cnlid,inwhsid,goodsid      unionall     selectinctmid,cnlid,inwhsid,sum(-1*qty) qty,goodsid     fromDtrBillRet a,DtrDetailRet b     wherea.billno=b.billno     groupbyinctmid,cnlid,inwhsid,goodsid)agroup by inctmid,cnlid,inwhsid,goodsid

3. First GROUP BY, then Union?

123456789

select inctmid,cnlid,inwhsid,sum(qty) qty,goodsidfrom DtrBill a,DtrDetail bwhere a.billno=b.billno group by inctmid,cnlid,inwhsid,goodsidunionselect inctmid,cnlid,inwhsid,sum(-1*qty) qty,goodsidfrom DtrBillRet a,DtrDetailRet bwhere a.billno=b.billnogroup by inctmid,cnlid,inwhsid,goodsid

4. Difference in execution plan: experiment 2,3 more than two branches of the hash matching operation 5. Results and conclusions of the CPU time-consuming conclusion 1 2275 362 high degree of parallelism, but the most CPU Resources 2 1622 416 degree of parallelism, the most CPU-saving resources (seemingly a compromise choice) 3 1811 507 the lowest degree of parallelism, consuming CPU resources medium

Reference:

Baidu UNION ALL GROUP BY

Go to SQL Union and SQL UNION ALL usage difference efficiency and issues with order by and group by mates