A UNION SQL query got me today.

[vbrad]

Sorry in advance if it isn't appropriate to put this here.

Say you have Select_Query_A UNION Select_Query_B
I understood that if both queries returned the same row that only one would appear in the recordset returned.
What I wasn't expecting was that the UNION will also removes duplicates within the results of each subquery.
But that seems to be the case.
Can someone confirm please if they know? Thanks

[qvb6]

Try UNION ALL?

https://learn.microsoft.com/en-us/sq...l-server-ver16

[vbrad]

Try UNION ALL?

https://learn.microsoft.com/en-us/sq...l-server-ver16

You are correct but it was the removal of the duplicates internal to the separate queries that caught me out, I wasn't expecting that to happen when I replaced 'Select * from' with 'select col1, col2, col3 from'

As far as I can see, UNION behaves like UNION ALL when the subqueries are of the form 'Select * from' or when your list of cols to be returned includes the primary key.

[Zvoni]

Sorry in advance if it isn't appropriate to put this here.

Say you have Select_Query_A UNION Select_Query_B
I understood that if both queries returned the same row that only one would appear in the recordset returned.
What I wasn't expecting was that the UNION will also removes duplicates within the results of each subquery.
But that seems to be the case.
Can someone confirm please if they know? Thanks

Of course UNION deduplicates: https://www.shiksha.com/online-cours...and-union-all/

The UNION command combines two or more SELECT statements but removes duplicates from the final result set,

In a nutshell it's the same as

SELECT DISTINCT * FROM 
  (SELECT SomeFields FROM Table1
   UNION ALL 
   SELECT SomeFields FROM Table2)

[vbrad]

Of course UNION deduplicates: https://www.shiksha.com/online-cours...and-union-all/



[/I]In a nutshell it's the same as

SELECT DISTINCT * FROM 
  (SELECT SomeFields FROM Table1
   UNION ALL 
   SELECT SomeFields FROM Table2)

Yes I know that now.
I had a UNION query that had worked for years purely because all the SELECTs in it were 'SELECT * from '
I'd just not imagined that returning fewer columns (by replacing the * with named cols) would also result in fewer rows and
couldn't understand that at first. It was a real gotcha for me.

[Zvoni]

Yes I know that now.
I had a UNION query that had worked for years purely because all the SELECTs in it were 'SELECT * from '
I'd just not imagined that returning fewer columns (by replacing the * with named cols) would also result in fewer rows and
couldn't understand that at first. It was a real gotcha for me.

Again a nail in the coffin of "SELECT * FROM...."

[vbrad]

Again a nail in the coffin of "SELECT * FROM...."

Or alternatively I know just enough SQL to be dangerous

[Zvoni]

Or alternatively I know just enough SQL to be dangerous

That's the thing: If you use a SELECT * FROM on a well formed Table (AutoInc Primary Key etc.), then you should never get duplicate records!
If you do get duplicates, then there is something seriously wrong with the table

Doing a SELECT * FROM on a well formed VIEW not withstanding

[vbrad]

That's the thing: If you use a SELECT * FROM on a well formed Table (AutoInc Primary Key etc.), then you should never get duplicate records!
If you do get duplicates, then there is something seriously wrong with the table

Doing a SELECT * FROM on a well formed VIEW not withstanding

Hi zvoni, not sure if we're talking at cross purposes.
The SELECT * wasn't returning duplicate records in my case because there was a pk.
For example if the full table was
id, surname, first
1, doe, john
2, doe, john

'SELECT surname, first from table' has a duplicate 'SELECT * from table' doesn't
SELECT left the duplicate in (as was desired since they're different john does) but the subsequent UNION stripped it out, which I hadn't expected because of lack of knowledge on my part.

[Zvoni]

Hi zvoni, not sure if we're talking at cross purposes.
The SELECT * wasn't returning duplicate records in my case because there was a pk.
For example if the full table was
id, surname, first
1, doe, john
2, doe, john

'SELECT surname, first from table' has a duplicate 'SELECT * from table' doesn't
SELECT left the duplicate in (as was desired since they're different john does) but the subsequent UNION stripped it out, which I hadn't expected because of lack of knowledge on my part.

Ahhhh.... OK, now i've got you.
Well, now you know: A UNION always goes on the FINAL resultset