Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3707
  • Last Modified:

UNION vs DISTINCT

Hi Experts,
I have a question regarding performance. I have a two tables that hold permissions for Groups.

tblA
------------
AID
PermissionTypeID
GroupID

tblB
-------------
BID
PermissionTypeID
GroupID

I would like to get all permissions from both tables into a single resultset. I can think of a different of ways of doing this.
1) Using UNION
SELECT     PermissionTypeID, GroupID
FROM         tblA
UNION
SELECT     PermissionTypeID, GroupID
FROM         tblB

2) Using DISTINCT
SELECT DISTINCT tblC.PermissionTypeID, tblC.GroupID FROM
(
SELECT     PermissionTypeID, GroupID
FROM         dbo.tblPermissionProduct
UNION ALL
SELECT     PermissionTypeID, GroupID
FROM         dbo.tblPermissionProductVersion
) As tblC

3) Using GROUP BY
SELECT PermissionTypeID, GroupID FROM
(
SELECT     PermissionTypeID, GroupID
FROM         dbo.tblPermissionProduct
UNION ALL
SELECT     PermissionTypeID, GroupID
FROM         dbo.tblPermissionProductVersion
)
GROUP BY PermissionTypeID, GroupID

Provided I do not want aggregates, just of list of unique entries of PermissionTypeID and GroupID, which method would perform best.

Thanks for your time.
0
MonCapitan
Asked:
MonCapitan
  • 3
  • 2
  • 2
  • +3
4 Solutions
 
aaronakinCommented:
You're better off with your first example using UNION.
0
 
chapmandewCommented:
They're going to perform basically the same.  UNION is the same as SELECT DISTINCT with a UNION ALL.  The group by basically does the same thing...
0
 
Kevin CrossChief Technology OfficerCommented:
Since the UNION already filters uniquely, it would be my choice.  Don't think you are gaining much performance wise, but from that standpoint, the UNION is the most simply query so why add overhead of derived queries to do DISTINCT or GROUP BY method you showed.  KISS principle.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
RiteshShahCommented:
your 1st example will give you better performance than other two. have you checked query plan for all? first will give you better picture I am sure.
0
 
segurahCommented:
Query 1 is the best one because :

Union=SELECT DISTINCT, then you are joining only PermissionTypeID,GroupID that aren't equals.

In option 2: You are doing a "union all", cardinality of "union all" vs "union" could be significantly bigger, then DBMS must do a distinct (another process). Result: More Memory, More Processing.

In Option 3: More or thess the same option 2 (because you use union all)

Resume: All querys do the sasme, option1 is optimal.
 
0
 
chapmandewCommented:
the first is easier to write, but in terms of performance,they're exactly the same...I promise.  :)
0
 
chapmandewCommented:
>>then DBMS must do a distinct (another process). Result: More Memory, More Processing.

It has to do that anyway, since a distinct result set is needed.
0
 
aaronakinCommented:
Chap is correct.  There is no difference in performance.  UNION is just easier to write.
0
 
nawsherCommented:
what about another table added? (tblC)

I think then the other options except union will come in handy. isn't it?
0
 
Kevin CrossChief Technology OfficerCommented:
Not really, add another UNION, no difference.   If you have a real life case that has issues with this, then you can try a related question but simply the answer would be the same whether using 2 or 3 tables. ;)
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now