UNION Question

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  July 19, 2017 at 12:07 pm

  #331882

  I can't believe I'm having to ask this, but I am noticing some incredibly odd behavior with a UNION that I'm using and I'm hoping someone can help me understand why.

  I have a table with about 420,000 records in it--and it only has one datetime field. Let's call it calendar.

  SELECT * FROM calendar --420,000 records

  
SELECT *
FROM calendar
UNION
SELECT *
FROM calendar

  --939 records


  How is that even possible? I must be missing something.

  Thanks,
  Mike

  Mike Scalise, PMP
  https://www.michaelscalise.com

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  July 19, 2017 at 12:24 pm

  #1951724

  Mike Scalise - Wednesday, July 19, 2017 12:07 PM

  I can't believe I'm having to ask this, but I am noticing some incredibly odd behavior with a UNION that I'm using and I'm hoping someone can help me understand why.

  I have a table with about 420,000 records in it--and it only has one datetime field. Let's call it calendar.

  SELECT * FROM calendar --420,000 records

  
SELECT *
FROM calendar
UNION
SELECT *
FROM calendar

  --939 records


  How is that even possible? I must be missing something.

  Thanks,
  Mike

  Union eliminates dupes, basically like doing a select distinct, and union all does not. Maybe try union all and see what you get.

  Sue

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  July 19, 2017 at 12:43 pm

  #1951729

  Sue_H - Wednesday, July 19, 2017 12:24 PM

  Mike Scalise - Wednesday, July 19, 2017 12:07 PM

  I can't believe I'm having to ask this, but I am noticing some incredibly odd behavior with a UNION that I'm using and I'm hoping someone can help me understand why.

  I have a table with about 420,000 records in it--and it only has one datetime field. Let's call it calendar.

  SELECT * FROM calendar --420,000 records

  
SELECT *
FROM calendar
UNION
SELECT *
FROM calendar

  --939 records


  How is that even possible? I must be missing something.

  Thanks,
  Mike

  Union eliminates dupes, basically like doing a select distinct, and union all does not. Maybe try union all and see what you get.

  Sue

  Sue,

  Thanks for the response. I know--I've been a database developer for nearly 10 years and have used UNION (and UNION ALL) on many occasions, but I've never seen it return results like that.

  When I use UNION ALL, I get 840,000 records, as expected.

  Any other ideas?

  Thanks,

  Mike

  Mike Scalise, PMP
  https://www.michaelscalise.com

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 19, 2017 at 12:55 pm

  #1951731

  based on what I believe you have said so far in this post...is the following a representative example? 

  SELECT TOP 420000
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2015', '2017'), '2015')
INTO   #TransData
FROM   sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

  SELECT * FROM #Transdata
UNION
SELECT * FROM #Transdata

  SELECT * FROM #Transdata
UNION ALL
SELECT * FROM #Transdata

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  July 19, 2017 at 1:00 pm

  #1951733

  J Livingston SQL - Wednesday, July 19, 2017 12:55 PM

  based on what I believe you have said so far in this post...is the following a representative example? 

  SELECT TOP 420000
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2015', '2017'), '2015')
INTO   #TransData
FROM   sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

  SELECT * FROM #Transdata
UNION
SELECT * FROM #Transdata

  SELECT * FROM #Transdata
UNION ALL
SELECT * FROM #Transdata

  Yes that is actually a perfect depiction of what's going on. In the case of the UNION in your example, I get 731 rows back. Do you have an explanation for why it's happening?

  Thanks in advance,

  Mike

  Mike Scalise, PMP
  https://www.michaelscalise.com

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 19, 2017 at 1:05 pm

  #1951735

  Mike Scalise - Wednesday, July 19, 2017 1:00 PM

  J Livingston SQL - Wednesday, July 19, 2017 12:55 PM

  based on what I believe you have said so far in this post...is the following a representative example? 

  SELECT TOP 420000
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2015', '2017'), '2015')
INTO   #TransData
FROM   sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

  SELECT * FROM #Transdata
UNION
SELECT * FROM #Transdata

  SELECT * FROM #Transdata
UNION ALL
SELECT * FROM #Transdata

  Yes that is actually a perfect depiction of what's going on. In the case of the UNION in your example, I get 731 rows back. Do you have an explanation for why it's happening?

  Thanks in advance,

  Mike

  Yep...as Sue said earlier "Union eliminates dupes, basically like doing a select distinct, and union all does not"

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  July 19, 2017 at 1:10 pm

  #1951736

  J Livingston SQL - Wednesday, July 19, 2017 1:05 PM

  Mike Scalise - Wednesday, July 19, 2017 1:00 PM

  J Livingston SQL - Wednesday, July 19, 2017 12:55 PM

  based on what I believe you have said so far in this post...is the following a representative example? 

  SELECT TOP 420000
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2015', '2017'), '2015')
INTO   #TransData
FROM   sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

  SELECT * FROM #Transdata
UNION
SELECT * FROM #Transdata

  SELECT * FROM #Transdata
UNION ALL
SELECT * FROM #Transdata

  Yes that is actually a perfect depiction of what's going on. In the case of the UNION in your example, I get 731 rows back. Do you have an explanation for why it's happening?

  Thanks in advance,

  Mike

  Yep...as Sue said earlier "Union eliminates dupes, basically like doing a select distinct, and union all does not"

  To go further UNION throws a distinct around the entire statement.  Since you're unioning the same table you are effectively just doing this, SELECT DISTINCT * FROM #Transdata

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 19, 2017 at 1:11 pm

  #1951737

  I have to ask why you are performing this query

  SELECT *
FROM calendar
UNION
SELECT *
FROM calendar

  are you by chance trying to obfuscate an actual real world query?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  July 19, 2017 at 1:35 pm

  #1951746

  I can't believe I missed that. I'm just not operating on all cylinders today.

  I hadn't done a distinct or group by on the table(s) before using the union so it didn't dawn on me that I had duplicates in each individual table.

  I know I used two of the exact same table but originally I was comparing results from two different ones and was just testing a theory by using the same one twice.

  Thanks again both of you for pointing that out.

  Mike Scalise, PMP
  https://www.michaelscalise.com

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 19, 2017 at 1:46 pm

  #1951750

  Mike Scalise - Wednesday, July 19, 2017 1:35 PM

  I can't believe I missed that. I'm just not operating on all cylinders today. I hadn't done a distinct or group by on the table(s) before using the union so it didn't dawn on me that I had duplicates in each individual table. I know I used two of the exact same table but originally I was comparing results from two different ones and was just testing a theory by using the same one twice. Thanks again both of you for pointing that out.

  Mike...we all have days like this !
  Glad to have been some help....

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• autoexcrement

  SSCertifiable

  Points: 5956

  More actions

  July 19, 2017 at 3:40 pm

  #1951767

  Might be a good time to add a useful primary key to your table. 🙂

  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

• Erwin Oosterhoorn

  SSCrazy

  Points: 2316

  More actions

  July 20, 2017 at 10:13 am

  #1951903

  I could see this becoming a QOTD 🙂