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
store_name |
sales |
date |
los Angeles |
$1500 |
jan-05-1999 |
san Diego |
$250 |
jan-07-19 |
los Angeles |
$300 | Td>
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
store_name |
sales |
date |
los Angeles |
$1500 |
jan-05-1999 |
san Diego |
$250 |
jan-07-19 |
los Angeles |
$300 | Td>
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,goodsid
from
(
select
inctmid,cnlid,inwhsid,qty,goodsid
from
DtrBill a,DtrDetail b
where
a.billno=b.billno
union
all
select
inctmid,cnlid,inwhsid,-1*qty qty,goodsid
from
DtrBillRet a,DtrDetailRet b
where
a.billno=b.billno
)a
group by inctmid,cnlid,inwhsid,goodsid
|
2. Group BY, then union all, then group by?
12345678910111213 |
select inctmid ctmid,cnlid,inwhsid whsid,
sum
(qty) qty,goodsid
from
(
select
inctmid,cnlid,inwhsid,
sum
(qty) qty,goodsid
from
DtrBill a,DtrDetail b
where
a.billno=b.billno
group
by
inctmid,cnlid,inwhsid,goodsid
union
all
select
inctmid,cnlid,inwhsid,
sum
(-1*qty) qty,goodsid
from
DtrBillRet a,DtrDetailRet b
where
a.billno=b.billno
group
by
inctmid,cnlid,inwhsid,goodsid
)a
group by inctmid,cnlid,inwhsid,goodsid
|
3. First GROUP BY, then Union?
123456789 |
select inctmid,cnlid,inwhsid,
sum
(qty) qty,goodsid
from DtrBill a,DtrDetail b
where a.billno=b.billno
group by inctmid,cnlid,inwhsid,goodsid
union
select inctmid,cnlid,inwhsid,
sum
(-1*qty) qty,goodsid
from DtrBillRet a,DtrDetailRet b
where a.billno=b.billno
group 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