Back
join (clj)
(source)function
(join & args)
Accepts one or more (INNER) JOIN expressions. Each
join expression is specified as a pair of arguments,
where the first one is the table name (or a pair of
table and alias) and the second one is the join
condition:
(join :table [:= :foo.id :table.foo_id])
(join [:table :t] [:= :foo.id :t.foo_id])
Produces:
INNER JOIN table ON foo.id = table.foo_id
INNER JOIN table AS t ON foo.id = t.foo_id
Examples
honeysql
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest set-after-join
(is (=
["UPDATE `foo` INNER JOIN `bar` ON `bar`.`id` = `foo`.`bar_id` SET `a` = ? WHERE `bar`.`b` = ?" 1 42]
(->
{:update :foo
:join [:bar [:= :bar.id :foo.bar_id]]
:set {:a 1}
:where [:= :bar.b 42]}
(format {:dialect :mysql}))))
;; issue 344
(is (=
["UPDATE `foo` INNER JOIN `bar` ON `bar`.`id` = `foo`.`bar_id` SET `f`.`a` = ? WHERE `bar`.`b` = ?" 1 42]
(->
{:update :foo
:join [:bar [:= :bar.id :foo.bar_id]]
;; do not drop ns in set clause for MySQL:
:set {:f/a 1}
:where [:= :bar.b 42]}
(format {:dialect :mysql})))))
(deftest delete-test
(is (= ["DELETE `t1`, `t2` FROM `table1` AS `t1` INNER JOIN `table2` AS `t2` ON `t1`.`fk` = `t2`.`id` WHERE `t1`.`bar` = ?" 42]
(-> {:delete [:t1 :t2]
:from [[:table1 :t1]]
:join [[:table2 :t2] [:= :t1.fk :t2.id]]
:where [:= :t1.bar 42]}
(format {:dialect :mysql})))))
;; Make sure if Locale is Turkish we're not generating queries like İNNER JOIN (dot over the I) because
;; `string/upper-case` is converting things to upper-case using the default Locale. Generated query should be the same
;; regardless of system Locale. See #236
#?(:clj
(deftest statements-generated-correctly-with-turkish-locale
(let [format-with-locale (fn [^String language-tag]
(let [original-locale (java.util.Locale/getDefault)]
(try
(java.util.Locale/setDefault (java.util.Locale/forLanguageTag language-tag))
(format {:select [:t2.name]
:from [[:table1 :t1]]
:join [[:table2 :t2] [:= :t1.fk :t2.id]]
:where [:= :t1.id 1]})
(finally
(java.util.Locale/setDefault original-locale)))))]
(is (= (format-with-locale "en")
(format-with-locale "tr"))))))
(deftest join-on-true-253
;; used to work on honeysql 0.9.2; broke in 0.9.3
(is (= ["SELECT foo FROM bar INNER JOIN table AS t ON TRUE"]
(format {:select [:foo]
:from [:bar]
:join [[:table :t] true]}))))
(deftest cross-join-test
(is (= ["SELECT * FROM foo CROSS JOIN bar"]
(format {:select [:*]
:from [:foo]
:cross-join [:bar]})))
(is (= ["SELECT * FROM foo AS f CROSS JOIN bar b"]
(format {:select [:*]
:from [[:foo :f]]
:cross-join [[:bar :b]]}))))
(deftest join-without-on-using
;; essentially issue 326
(testing "join does not need on or using"
(is (= ["SELECT foo FROM bar INNER JOIN quux"]
(format {:select :foo
:from :bar
:join [:quux]}))))
(testing "join on select with parameters"
(is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) WHERE id = ?" 123 456]
(format {:select :foo
:from :bar
:join [{:select :a :from :b :where [:= :id 123]}]
:where [:= :id 456]})))
(is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) AS x WHERE id = ?" 123 456]
(format {:select :foo
:from :bar
:join [[{:select :a :from :b :where [:= :id 123]} :x]]
:where [:= :id 456]})))
(is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) AS x ON y WHERE id = ?" 123 456]
(format {:select :foo
:from :bar
:join [[{:select :a :from :b :where [:= :id 123]} :x] :y]
:where [:= :id 456]})))))
(deftest issue-483-join
(testing "single nested join"
(is (= ["SELECT * FROM tbl1 LEFT JOIN (tbl2 INNER JOIN tbl3 USING (common_column)) ON (tbl2.col2 = tbl1.col2) AND (tbl3.col3 = tbl1.col3)"]
(-> {:select :*
:from :tbl1
:left-join [[[:join :tbl2 {:join [:tbl3 [:using [:common_column]]]}]]
[:and
[:= :tbl2.col2 :tbl1.col2]
[:= :tbl3.col3 :tbl1.col3]]]}
(sut/format)))))
(testing "multiple nested join"
(is (= ["SELECT * FROM tbl1 LEFT JOIN (tbl2 INNER JOIN tbl3 USING (common_column) RIGHT JOIN tbl4 USING (id)) ON (tbl2.col2 = tbl1.col2) AND (tbl3.col3 = tbl1.col3)"]
(-> {:select :*
:from :tbl1
:left-join [[[:join :tbl2
{:join [:tbl3 [:using [:common_column]]]}
{:right-join [:tbl4 [:using :id]]}]]
[:and
[:= :tbl2.col2 :tbl1.col2]
[:= :tbl3.col3 :tbl1.col3]]]}
(sut/format)))))
(testing "special syntax example"
(is (= ["INNER JOIN (tbl1 LEFT JOIN tbl2 USING (id))"]
(sut/format {:join [[[:join :tbl1 {:left-join [:tbl2 [:using :id]]}]]]})))))
honeysql
(ns honey.sql.helpers-test
(:refer-clojure :exclude [filter for group-by partition-by set update])
(:require [clojure.test :refer [deftest is testing]]
[honey.sql :as sql]
[honey.sql.helpers :as h
:refer [add-column add-index alter-table columns create-table create-table-as create-view
create-materialized-view drop-view drop-materialized-view
create-index
bulk-collect-into
cross-join do-update-set drop-column drop-index drop-table
filter from full-join
group-by having insert-into
join-by join lateral left-join limit offset on-conflict
on-duplicate-key-update
order-by over partition-by refresh-materialized-view
rename-column rename-table returning right-join
select select-distinct select-top select-distinct-top
values where window with with-columns
with-data within-group]]))
(deftest test-select
(testing "large helper expression"
(let [m1 (-> (with [:cte (-> (select :*)
(from :example)
(where [:= :example-column 0]))])
(select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
:%now [[:raw "@x := 10"]])
(from [:foo :f] [:baz :b])
(join :draq [:= :f.b :draq.x])
(left-join [:clod :c] [:= :f.a :c.d])
(right-join :bock [:= :bock.z :c.e])
(full-join :beck [:= :beck.x :c.y])
(where [:or
[:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
[:and [:< 1 2] [:< 2 3]]
[:in :f.e [1 [:param :param2] 3]]
[:between :f.e 10 20]])
(group-by :f.a)
(having [:< 0 :f.e])
(order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
(limit 50)
(offset 10))
m2 {:with [[:cte {:select [:*]
:from [:example]
:where [:= :example-column 0]}]]
:select-distinct [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
:%now [[:raw "@x := 10"]]]
:from [[:foo :f] [:baz :b]]
:join [:draq [:= :f.b :draq.x]]
:left-join [[:clod :c] [:= :f.a :c.d]]
:right-join [:bock [:= :bock.z :c.e]]
:full-join [:beck [:= :beck.x :c.y]]
:where [:or
[:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
[:and [:< 1 2] [:< 2 3]]
[:in :f.e [1 [:param :param2] 3]]
[:between :f.e 10 20]]
:group-by [:f.a]
:having [:< 0 :f.e]
:order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]]
:limit 50
:offset 10}]
(testing "Various construction methods are consistent"
(is (= m1 m2)))
(testing "SQL data formats correctly"
(is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ?"
0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
(sql/format m1 {:params {:param1 "gabba" :param2 2}}))))
#?(:clj (testing "SQL data prints and reads correctly"
(is (= m1 (read-string (pr-str m1))))))
#_(testing "SQL data formats correctly with alternate param naming"
(is (= (sql/format m1 {:params {:param1 "gabba" :param2 2}})
["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = $2) AND (b.baz <> $3)) OR (($4 < $5) AND ($6 < $7)) OR (f.e IN ($8, $9, $10)) OR f.e BETWEEN $11 AND $12 GROUP BY f.a HAVING $13 < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT $14 OFFSET $15"
0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10])))
(testing "Locking"
(is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS `bla-bla`, NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ? LOCK IN SHARE MODE"
0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
(sql/format (assoc m1 :lock [:in-share-mode])
{:params {:param1 "gabba" :param2 2}
;; to enable :lock
:dialect :mysql :quoted false}))))))
(testing "large helper expression with simplified where"
(let [m1 (-> (with [:cte (-> (select :*)
(from :example)
(where := :example-column 0))])
(select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
:%now [[:raw "@x := 10"]])
(from [:foo :f] [:baz :b])
(join :draq [:= :f.b :draq.x])
(left-join [:clod :c] [:= :f.a :c.d])
(right-join :bock [:= :bock.z :c.e])
(full-join :beck [:= :beck.x :c.y])
(where :or
[:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
[:and [:< 1 2] [:< 2 3]]
[:in :f.e [1 [:param :param2] 3]]
[:between :f.e 10 20])
(group-by :f.a)
(having :< 0 :f.e)
(order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
(limit 50)
(offset 10))
m2 {:with [[:cte {:select [:*]
:from [:example]
:where [:= :example-column 0]}]]
:select-distinct [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
:%now [[:raw "@x := 10"]]]
:from [[:foo :f] [:baz :b]]
:join [:draq [:= :f.b :draq.x]]
:left-join [[:clod :c] [:= :f.a :c.d]]
:right-join [:bock [:= :bock.z :c.e]]
:full-join [:beck [:= :beck.x :c.y]]
:where [:or
[:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
[:and [:< 1 2] [:< 2 3]]
[:in :f.e [1 [:param :param2] 3]]
[:between :f.e 10 20]]
:group-by [:f.a]
:having [:< 0 :f.e]
:order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]]
:limit 50
:offset 10}]
(testing "Various construction methods are consistent"
(is (= m1 m2)))
(testing "SQL data formats correctly"
(is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ?"
0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
(sql/format m1 {:params {:param1 "gabba" :param2 2}}))))
#?(:clj (testing "SQL data prints and reads correctly"
(is (= m1 (read-string (pr-str m1))))))
#_(testing "SQL data formats correctly with alternate param naming"
(is (= (sql/format m1 {:params {:param1 "gabba" :param2 2}})
["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = $2) AND (b.baz <> $3)) OR (($4 < $5) AND ($6 < $7)) OR (f.e IN ($8, $9, $10)) OR f.e BETWEEN $11 AND $12 GROUP BY f.a HAVING $13 < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT $14 OFFSET $15"
0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10])))
(testing "Locking"
(is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS `bla-bla`, NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ? LOCK IN SHARE MODE"
0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
(sql/format (assoc m1 :lock [:in-share-mode])
{:params {:param1 "gabba" :param2 2}
;; to enable :lock
:dialect :mysql :quoted false})))))))
(deftest from-expression-tests
(testing "FROM can be a function invocation"
(is (= ["SELECT foo, bar FROM F(?) AS x" 1]
(sql/format {:select [:foo :bar] :from [[[:f 1] :x]]}))))
;; these two examples are from https://www.postgresql.org/docs/9.3/queries-table-expressions.html#QUERIES-LATERAL
(testing "FROM can be a LATERAL select"
(is (= ["SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) AS ss"]
(sql/format {:select :*
:from [:foo
[[:lateral {:select :*
:from :bar
:where [:= :bar.id :foo.bar_id]}] :ss]]}))))
(testing "FROM can be a LATERAL expression"
(is (= [(str "SELECT p1.id, p2.id, v1, v2"
" FROM polygons AS p1, polygons AS p2,"
" LATERAL VERTICES(p1.poly) AS v1,"
" LATERAL VERTICES(p2.poly) AS v2"
" WHERE ((v1 <-> v2) < ?) AND (p1.id <> p2.id)") 10]
(sql/format {:select [:p1.id :p2.id :v1 :v2]
:from [[:polygons :p1] [:polygons :p2]
[[:lateral [:vertices :p1.poly]] :v1]
[[:lateral [:vertices :p2.poly]] :v2]]
:where [:and [:< [:<-> :v1 :v2] 10] [:!= :p1.id :p2.id]]})))
(is (= [(str "SELECT m.name"
" FROM manufacturers AS m"
" LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE"
" WHERE pname IS NULL")]
(sql/format {:select :m.name
:from [[:manufacturers :m]]
:left-join [[[:lateral [:get_product_names :m.id]] :pname] true]
:where [:= :pname nil]})))))
(deftest join-by-test
(testing "Natural JOIN orders"
(is (= ["SELECT * FROM foo INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y"]
(sql/format {:select [:*] :from [:foo]
:full-join [:beck [:= :beck.x :c.y]]
:right-join [:bock [:= :bock.z :c.e]]
:left-join [[:clod :c] [:= :f.a :c.d]]
:join [:draq [:= :f.b :draq.x]]}))))
(testing "Specific JOIN orders"
(is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
(sql/format {:select [:*] :from [:foo]
:join-by [:full [:beck [:= :beck.x :c.y]]
:right [:bock [:= :bock.z :c.e]]
:left [[:clod :c] [:= :f.a :c.d]]
:join [:draq [:= :f.b :draq.x]]]})))
(is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
(-> (select :*)
(from :foo)
(join-by :full-join [:beck [:= :beck.x :c.y]]
:right-join [:bock [:= :bock.z :c.e]]
:left-join [[:clod :c] [:= :f.a :c.d]]
:inner-join [:draq [:= :f.b :draq.x]])
(sql/format)))))
(testing "Specific JOIN orders with join clauses"
(is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
(sql/format {:select [:*] :from [:foo]
:join-by [{:full-join [:beck [:= :beck.x :c.y]]}
{:right-join [:bock [:= :bock.z :c.e]]}
{:left-join [[:clod :c] [:= :f.a :c.d]]}
{:join [:draq [:= :f.b :draq.x]]}]})))
(is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
(-> (select :*)
(from :foo)
(join-by (full-join :beck [:= :beck.x :c.y])
(right-join :bock [:= :bock.z :c.e])
(left-join [:clod :c] [:= :f.a :c.d])
(join :draq [:= :f.b :draq.x]))
(sql/format))))))
(deftest join-test
(testing "nil join"
(is (= ["SELECT * FROM foo INNER JOIN x ON foo.id = x.id INNER JOIN y"]
(-> (select :*)
(from :foo)
(join :x [:= :foo.id :x.id] :y nil)
sql/format)))))
(deftest join-using-test
(testing "nil join"
(is (= ["SELECT * FROM foo INNER JOIN x USING (id) INNER JOIN y USING (foo, bar)"]
(-> (select :*)
(from :foo)
(join :x [:using :id] :y [:using :foo :bar])
sql/format)))))
(deftest custom-helpers-test
(testing "nil join"
(is (= ["UPDATE foo SET bar = ? WHERE quux = ?" 1 2]
(-> (my-update :foo)
(my-set {:bar 1})
(my-where (sql/map= {:quux 2}))
sql/format)))))
(deftest cross-join-test
(is (= ["SELECT * FROM foo CROSS JOIN bar"]
(-> (select :*)
(from :foo)
(cross-join :bar)
sql/format)))
(is (= ["SELECT * FROM foo AS f CROSS JOIN bar b"]
(-> (select :*)
(from [:foo :f])
(cross-join [:bar :b])
sql/format))))
honeysql
(ns honey.cache-test
(:refer-clojure :exclude [format group-by])
(:require [clojure.core.cache.wrapped :as cache]
[clojure.test :refer [deftest is]]
[honey.sql :as sut]
[honey.sql.helpers
:refer [select-distinct from join left-join right-join where
group-by having order-by limit offset]]))
(def big-complicated-map
(-> (select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
[[:now]] [[:raw "@x := 10"]])
(from [:foo :f] [:baz :b])
(join :draq [:= :f.b :draq.x]
:eldr [:= :f.e :eldr.t])
(left-join [:clod :c] [:= :f.a :c.d])
(right-join :bock [:= :bock.z :c.e])
(where [:or
[:and [:= :f.a "bort"] [:not= :b.baz [:param :param1]]]
[:and [:< 1 2] [:< 2 3]]
[:in :f.e [1 [:param :param2] 3]]
[:between :f.e 10 20]])
(group-by :f.a :c.e)
(having [:< 0 :f.e])
(order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
(limit 50)
(offset 10)))