in reply to Re: sql output format
in thread sql output format

A simple comment on your excellent post. As a general rule, it is better to use UNION ALL. UNION applies a DISTINCT which forces a(n expensive) sort operation.

Replies are listed 'Best First'.
Re^3: sql output format
by roboticus (Chancellor) on Oct 20, 2014 at 19:07 UTC

    chacham:

    Thanks for that. I wasn't aware of the difference between UNION and UNION ALL. Hopefully, I'll remember it next time I need it.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]

      lmgtfy, for

      PostgreSQL, Oracle, and SQLite.

      As chacham said, UNION ALL leaves the rowsets from all query-parts intact; UNION is slightly more expensive, as it makes the final rows DISTINCT (i.e., removes duplicates).

      With regard to these compound query operators, note that Oracle stubbornly uses MINUS instead of the Standard-prescribed EXCEPT. Ah well, I guess it's too late to teach them manners ;-)

[reply]

In Section Seekers of Perl Wisdom