CODEWITHSUNDEEP
mysqlsql
akkarin
akkarin

Reputation: 131

SQL – Eliminating duplicates in UNION ALL using a WHERE clause

On toptal.com I found this question to which they provided the following solution:

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

Can someone explain the usage of X and Y here? Are these variables? Does it also work this way in MySQL? I can't seem to get this kind of query running on a test server.

Also, they make the following claim:

The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command, while avoiding much of its performance hit.

But if this were true, why would anyone ever use UNION DISTINCT? In particular, why wouldn't it be implemented using this supposedly more efficient way?

Upvotes: 0

Views: 732

Answers (2)

fauxmosapien
fauxmosapien

Reputation: 535

The x is just a placeholder in that pseudocode for the 'real' filter. You might know that the field a is the only one that might be duplicated on both sides of your union, but the query optimiser might not, so doing the union in that way is more performance friendly. That answer would only apply in certain circumstances, depending on the context of the data.

It's not a well written question.

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can use a OR in the WHERE clause like this:

SELECT * 
FROM mytable 
WHERE a=X  OR (b=Y AND a!=X);

Upvotes: 0

Related Questions