Outer Joins

In the relational database world, outer joins allow you to return relations even when data is missing from one side of a join. For example, you might want all of the Mbrainz artists and their start years, including artists who do not even have a start year.

In Datomic, you can find the entities you want with datalog, and then make an independent decision about which details you want to pull. The following example uses a query to find the artists, and then plugs in a pull pattern to get both the artist name and start year:

(def find-expr '[:find (pull ?e details-expr)
                 :in $ details-expr
                 :where [?e :artist/name]])
(def details-expr [:artist/name :artist/startYear])
(d/q find-expr db details-expr)

The query/pull separation also makes it easy to reuse query and pull logic independently.

Datomic also includes the get-else query function, which is closer to a literal outer join in that you can reference a possibly-missing attribute directly in the datalog, specifying an alternate value when the attribute is missing. The example below replaces a missing start year with "Unknown":

[:find ?e ?name ?year
 :where
 [?e :artist/name ?name]
 [(get-else $ ?e :artist/startYear "Unknown") ?year]]

Check the full code for this example in the Day of Datomic Cloud repo.