当 SQL 语句需要UNION 两个查询结果集合时,即使检索结果中不会有重复的记录,如果使用UNION这两个结果集同样会尝试进行合并,然后在输出最终结果前进行排序。因此,如果检索结果中不会有重复的记录的话,应该用UNION ALL替代UNION,这样效率就会因此得到提高。下面的语句2 就比语句1效率更加高。
语句 1:
复制代码
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS1
WHERE TRAN_DATE = '20010101'
UNION
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS2
WHERE TRAN_DATE ='20010102'
复制代码
语句2:
复制代码
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS1
WHERE TRAN_DATE ='20010101'
UNION ALL
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS2
WHERE TRAN_DATE = '20010102'
复制代码
语句 1:
复制代码
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS1
WHERE TRAN_DATE = '20010101'
UNION
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS2
WHERE TRAN_DATE ='20010102'
复制代码
语句2:
复制代码
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS1
WHERE TRAN_DATE ='20010101'
UNION ALL
SELECTACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS2
WHERE TRAN_DATE = '20010102'
复制代码