Most of them working in development or SQL environment would
have come across UNION and UNION ALL. But, when we look into the cause of using
UNION and UNION ALL, it’s pretty interesting not just in terms of results, but
also in terms of performance.
Basic difference between UNION and UNION ALL is that, UNION
ALL results in almost all the duplicates between the tables, whereas UNION
omits from being resulting the duplicate values.
Secondary difference and advantage would be getting the results
at faster rate. The results will be generated at a pretty faster rate for UNION
ALL when compared to UNION. However, the UNION ALL generating the duplicate
results can eventually lead to long time querying.
For example, let’s take count of a table name CUS from two
databases, one without duplicates and the other with duplicate counts. Take a
look at the script below:
FOR UNION:
SELECT '-----CUSName',0
UNION
SELECT 'Customer1',COUNT(*) FROM CUS
UNION
SELECT 'Customer1',COUNT(*) FROM CUS
Another scenario is to
use UNION ALL instead of UNION in the statements to see a different result in
the output set.
UNION ALL:
SELECT '-----CUSName',0
UNION ALL
SELECT 'Customer1',COUNT(*) FROM CUS
UNION
SELECT 'Customer1',COUNT(*) FROM CUS
From the above outputs, it is clear that UNION ALL functions in selecting even the duplicate counts. Seeing the example, Customer2 consists of 10 duplicate records, which was put forward along as a redundancy.
You may also need: "10 New Features SQL Server 2016 Does Better than SQL Server 2014"
You may also need: "10 New Features SQL Server 2016 Does Better than SQL Server 2014"
0 comments:
Post a Comment