Gj’s SQL blog

April 19, 2009

Unions and order

Filed under: postgresql, unions — Grzegorz Jaskiewicz @ 2:45 am

Quick note, on something that might save you few minutes.

It’s about UNION [ALL]. And why you should never assume order of output to be predetermined by order of tables in query.

So, to quickly describe – what unions are useful for.

Say we have two tables, on surface unrelated, or maybe they are members of the same cluster.

Or maybe, we have tables with words. Completely unrelated, up until now. We have to select most frequently used words, from both tables – and produce single output for that.

table1: word, id

table2: word, id,

For simplicity sake,:

(SELECT t.word FROM table1 t JOIN freqs f ON t.id=f.wid ORDER BY f.val DESC LIMIT 1)
UNION
(SELECT t.word FROM table2 t JOIN freqs f ON t.id=f.wid ORDER BY f.val DESC LIMIT 1);

Very simple, really.

But one gotcha here, is that you will probably expect these two guys to produce two rows, and two rows it will (most likely) be.

But the order, won’t be necessary what you would expect here. Why ?

Postgresql, starting from 8.4 will use hash aggregate to sort these guys. UNION (without ALL) will only return rows that are distinctive.

So that hash aggregate in it self is used to sort output, and remove duplicates quickly. No attempt will be made to restore the order, and SQL spec doesn’t enforce DBE to maintain ‘original’ order.

So, to ensure you get what you want, ORDER BY  has to be used. For instance , like that:

SELECT word FROM (

  (SELECT t.word, 1 AS tid FROM table1 t JOIN freqs f ON t.id=f.wid ORDER BY f.val DESC LIMIT 1)
  UNION
  (SELECT t.word, 2 AS tid FROM table2 t JOIN freqs f ON t.id=f.wid ORDER BY f.val DESC LIMIT 1)

) f

ORDER BY f.tid

Or even better, use UNION ALL – but that will introduce risk of duplicated results – which is simply what you are trying to avoid in first place, if not using ‘ALL’;

HTH, Bye.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: