Using UNION with Sequel

Question

I want to define a SQL-command like this:

SELECT * FROM WOMAN
UNION
SELECT * FROM MEN

I tried to define this with the following code sequence in Ruby + Sequel:

require 'sequel'

DB = Sequel::Database.new()
sel = DB[:women].union(DB[:men])
puts sel.sql

The result is (I made some pretty print on the result):

SELECT * FROM (
    SELECT * FROM `women` 
    UNION 
    SELECT * FROM `men`
) AS 't1'

There is an additional (superfluous?) SELECT.

If I define multiple UNION like in this code sample

sel = DB[:women].union(DB[:men]).union(DB[:girls]).union(DB[:boys])
puts sel.sql

I get more superfluous SELECTs.

SELECT * FROM (
  SELECT * FROM (
    SELECT * FROM (
      SELECT * FROM `women` 
      UNION 
      SELECT * FROM `men`
    ) AS 't1' 
    UNION 
    SELECT * FROM `girls`
  ) AS 't1' 
  UNION
  SELECT * FROM `boys`
) AS 't1'

I detected no problem with it up to now, the results seem to be the same.

My questions:

• Is there a reason for the additional selects (beside sequel internal procedures)
• Can I avoid the selects?
• Can I get problems with this additional selects? (Any Performance issue?)

Parkins

Answer 1

The reason for the extra SELECTs is so code like DB[:girls].union(DB[:boys]).where(:some_column=>1) operates properly. You can use DB[:girls].union(DB[:boys], :from_self=>false) to not wrap it in the extra SELECTs, as mentioned in the documentation.

Isentropic

Thanks. I'm always astonished, how good sequel works - and that I always miss it from the documentation (I'm sure I looked at the union documentation). – Parkins