`clojure.set/join`¶

Type: function Added: Clojure 1.0 Examples: 3 Runnable: 0

([xrel yrel] [xrel yrel km])

When passed 2 rels, returns the rel corresponding to the natural join. When passed an additional keymap, joins on the corresponding keys.

Examples¶

by Brian Marick on 2010-07-11 . May have evaluation errors.

;; This simple example shows each element of the first relation joined
;; with each element of the second (because they have no columns in common):

(def first-relation #{ {:a 1} {:a 2} })
(def second-relation #{ {:b 1} {:b 2} })
(join first-relation second-relation)
#{{:b 1, :a 1} 
  {:b 2, :a 1} 
  {:b 1, :a 2} 
  {:b 2, :a 2}}

;; Here's a larger example, in which a relation mainly about animal ownership
;; is joined with a relation about animal personality. The join is used to 
;; produce a relation joining information about an animal's personality to 
;; that animal.

(def animals #{{:name "betsy" :owner "brian" :kind "cow"}
                      {:name "jake"  :owner "brian" :kind "horse"}
                      {:name "josie" :owner "dawn"  :kind "cow"}})

(def personalities #{{:kind "cow" :personality "stoic"}
                            {:kind "horse" :personality "skittish"}})
#'user/personalities
(join animals personalities)

#{{:owner "dawn",  :name "josie", :kind "cow",   :personality "stoic"}
  {:owner "brian", :name "betsy", :kind "cow",   :personality "stoic"}
  {:owner "brian", :name "jake",  :kind "horse", :personality "skittish"}}

;; (If cows had two personalities, instead of one, each cow would have 
;; two rows in the output.)

;; Suppose `personalities` used `:species` instead of `:kind`:

(def personalities #{{:species "cow" :personality "stoic"}
                             {:species "horse" :personality "skittish"}})

;; A simple join would produce results like this:

(join animals personalities)
#{{:kind "horse", :owner "brian", :name "jake", :species "cow", :personality "stoic"}
  {:kind "cow", :owner "dawn", :name "josie", :species "cow", :personality "stoic"}
  {:kind "horse", :owner "brian", :name "jake", :species "horse", :personality "skittish"}
  {:kind "cow", :owner "brian", :name "betsy", :species "cow", :personality "stoic"}
  {:kind "cow", :owner "dawn", :name "josie", :species "horse", :personality "skittish"}
  {:kind "cow", :owner "brian", :name "betsy", :species "horse", :personality "skittish"}}

;; Notice that "Jake" is both a horse and a cow in the first line. That's 
;; likely not what you want. You can tell `join` to only produce output 
;; where the `:kind` value is the same as the `:species` value like this:

(join animals personalities {:kind :species})
#{{:kind "cow", :owner "dawn", :name "josie", :species "cow", :personality "stoic"}
  {:kind "horse", :owner "brian", :name "jake", :species "horse", :personality "skittish"}
  {:kind "cow", :owner "brian", :name "betsy", :species "cow", :personality "stoic"}}

;; Notice that the `:kind` and `:species` keys both appear in each output map.

by tomaskulich on 2017-05-04 . May have evaluation errors.

by reborg on 2018-08-10 . May have evaluation errors.

;; join with rename of (otherwise clashing) keys. Note that in the following
;; relations, we have "user.type" and "account.type"

(def users
  #{{:user-id 1 :name "john"   :age 22 :type "personal"}
    {:user-id 2 :name "jake"   :age 28 :type "company"}
    {:user-id 3 :name "amanda" :age 63 :type "personal"}})

(def accounts
  #{{:acc-id 1 :user-id 1 :amount 300.45 :type "saving"}
    {:acc-id 2 :user-id 2 :amount 1200.0 :type "saving"}
    {:acc-id 3 :user-id 1 :amount 850.1 :type "debit"}})

(require '[clojure.set :as s])

;; Clojure equivalent of the SQL:
;; SELECT users.user-id, accounts.acc-id, 
;;        users.type as type, accounts.type as atype
;; FROM users
;; INNER JOIN accounts ON users.user-id = accounts.user-id;

(s/project
  (s/join users (s/rename accounts {:type :atype}))
  [:user-id :acc-id :type :atype])

;; #{{:user-id 1, :acc-id 1, :type "personal", :atype "saving"}
;;   {:user-id 2, :acc-id 2, :type "company", :atype "saving"}
;;   {:user-id 1, :acc-id 3, :type "personal", :atype "debit"}}

clojure.set/join¶

Examples¶

See also¶

`clojure.set/join`¶