Search code examples
sqlslick

Wrong example in Slick docs?

Either I am unfocused today or Slick's Getting Started docs are not well-designed. So in the Queries/Unions section they have this snippet:

val q1 = coffees.filter(_.price < 8.0)
val q2 = coffees.filter(_.price > 9.0)

val unionQuery = q1 union q2
// compiles to SQL (simplified):
//   select x8."COF_NAME", x8."SUP_ID", x8."PRICE", x8."SALES", x8."TOTAL"
//     from "COFFEES" x8
//     where x8."PRICE" < 8.0
//   union select x9."COF_NAME", x9."SUP_ID", x9."PRICE", x9."SALES", x9."TOTAL"
//     from "COFFEES" x9
//     where x9."PRICE" > 9.0

val unionAllQuery = q1 ++ q2
// compiles to SQL (simplified):
//   select x8."COF_NAME", x8."SUP_ID", x8."PRICE", x8."SALES", x8."TOTAL"
//     from "COFFEES" x8
//     where x8."PRICE" < 8.0
//   union all select x9."COF_NAME", x9."SUP_ID", x9."PRICE", x9."SALES", x9."TOTAL"
//     from "COFFEES" x9
//     where x9."PRICE" > 9.0

And then they say: Unlike union which filters out duplicate values, ++ simply concatenates the results of the individual queries, which is usually more efficient.

I think there are no duplicates produced by q1 and q2. So it is either a wrong queries examples they provided to illustrate real difference between union and ++ or I don't get something important. Could you guys help?

Solution

  • In this specific case there are no duplicates as there is no intersection between q1 and q2. Maybe changing the queries to

    val q1 = coffees.filter(_.price < 8.0)
val q2 = coffees.filter(_.price < 9.0)

    would have been a better example. Anyway, the bottom line is this:

    • q1 union q2 translates to SQL UNION
    • q1 ++ q2 translates to SQL UNION ALL