=> (def q1 (select :* :from :t1))
=> (def q2 (select :* :from :t2))
=> (def q3 (select :* :from :t3))
=> (def q4 (select :* :from :t4))
=> (def q5 (select :* :from :t5))
A compound select statement is one that uses any of the set operations UNION, INTERSECT, or EXCEPT.
For the following descriptions, assume we have defined the following five select statements and that each table has the same column definitions.
=> (def q1 (select :* :from :t1))
=> (def q2 (select :* :from :t2))
=> (def q3 (select :* :from :t3))
=> (def q4 (select :* :from :t4))
=> (def q5 (select :* :from :t5))
=> (union$ q1 q2 q3)
"SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3;"
=> (union-all$ q1 q2 q3)
"SELECT * FROM t1 UNION ALL SELECT * FROM t2 UNION ALL SELECT * FROM t3;"
=> (intersect$ q1 q2 q3)
"SELECT * FROM t1 INTERSECT SELECT * FROM t2 INTERSECT SELECT * FROM t3;"
=> (intersect-all$ q1 q2 q3)
"SELECT * FROM t1 INTERSECT ALL SELECT * FROM t2 INTERSECT ALL SELECT * FROM t3;"
=> (except$ q1 q2 q3)
"SELECT * FROM t1 EXCEPT SELECT * FROM t2 EXCEPT SELECT * FROM t3;"
The above constructs are just syntactic sugar for an arbitrary compound select which can combine multiple set operations.
Here they are using compound-select
:
(compound-select q1 (union q2 q3))
(compound-select q1 (union-all q2 q3))
(compound-select q1 (intersect q2 q3))
(compound-select q1 (intersect-all q2 q3))
(compound-select q1 (except q2 q3))
Here’s an example that uses multiple set operations (I wrapped the string to make it more readable):
=> (compound-select$ q1 (union q2 q3) (intersect q4) (except q5))
"SELECT * FROM t1
UNION SELECT * FROM t2 UNION SELECT * FROM t3
INTERSECT SELECT * FROM t4
EXCEPT SELECT * FROM t5;"
Any of the compound select flavors above can be used with the order-by
, limit
, and offset
from a simple select
.
For example:
=> (union$ q1 q2 (order-by (asc :f1) :f2 (desc :f3)) (limit 3) (offset 5))
"SELECT * FROM t1 UNION SELECT * FROM t2
ORDER BY f1 ASC, f2, f3 DESC
LIMIT 3 OFFSET 5;"
Can you improve this documentation?Edit on GitHub
cljdoc is a website building & hosting documentation for Clojure/Script libraries
× close