OPTIONAL — left outer join

OPTIONAL { … } adds the pattern's bindings when it matches and keeps the solution when it doesn't. Variables that only the optional part binds come back as null.

Examples use the sample data.

Example

SELECT * FROM pgrdf.sparql($$
  PREFIX foaf: <http://xmlns.com/foaf/0.1/>
  SELECT ?name ?mbox
  WHERE { ?p foaf:name ?name
          OPTIONAL { ?p foaf:mbox ?mbox } }
  ORDER BY ?name
$$);
--  {"mbox": "mailto:alice@example.org", "name": "Alice"}
--  {"mbox": null, "name": "Bob"}
--  {"mbox": "mailto:carol@example.org", "name": "Carol"}

Rows where the optional part is missing

Add FILTER(!BOUND(?v)) to keep only the solutions where the optional pattern did not match:

SELECT * FROM pgrdf.sparql($$
  PREFIX foaf: <http://xmlns.com/foaf/0.1/>
  SELECT ?name
  WHERE { ?p foaf:name ?name
          OPTIONAL { ?p foaf:mbox ?mbox }
          FILTER(!BOUND(?mbox)) }
$$);
--  {"name": "Bob"}

pgRDF does not support FILTER NOT EXISTS; this pattern and MINUS are the two ways to express it.

Several patterns in one OPTIONAL

The patterns inside one OPTIONAL must match together, or none of them binds:

SELECT * FROM pgrdf.sparql($$
  PREFIX foaf: <http://xmlns.com/foaf/0.1/>
  PREFIX ex:   <http://example.org/>
  SELECT ?name ?mbox ?age
  WHERE { ?p foaf:name ?name
          OPTIONAL { ?p foaf:mbox ?mbox . ?p ex:age ?age } }
  ORDER BY ?name
$$);
--  {"age": "34", "mbox": "mailto:alice@example.org", "name": "Alice"}
--  {"age": null, "mbox": null, "name": "Bob"}
--  {"age": "29", "mbox": "mailto:carol@example.org", "name": "Carol"}

Bob has an age but no mailbox, so neither value comes back for him. To fetch each property independently, write one OPTIONAL per property.

Nested OPTIONAL

An OPTIONAL can contain another one. Here: each person, their friends if any, and each friend's mailbox if they have one.

SELECT * FROM pgrdf.sparql($$
  PREFIX foaf: <http://xmlns.com/foaf/0.1/>
  SELECT ?name ?friend ?fmbox
  WHERE { ?p foaf:name ?name
          OPTIONAL { ?p foaf:knows ?friend
                     OPTIONAL { ?friend foaf:mbox ?fmbox } } }
  ORDER BY ?name ?friend
$$);
--  {"name": "Alice", "fmbox": null, "friend": "http://example.org/bob"}
--  {"name": "Alice", "fmbox": "mailto:carol@example.org", "friend": "http://example.org/carol"}
--  {"name": "Bob", "fmbox": "mailto:carol@example.org", "friend": "http://example.org/carol"}
--  {"name": "Carol", "fmbox": null, "friend": null}

UNION inside OPTIONAL

OPTIONAL { { … } UNION { … } } is refused with an error. Write one OPTIONAL per alternative, each binding its own variable, and combine the columns in SQL:

SELECT sparql->>'name' AS name,
       coalesce(sparql->>'mbox', sparql->>'phone') AS contact
  FROM pgrdf.sparql($$
    PREFIX foaf: <http://xmlns.com/foaf/0.1/>
    PREFIX ex:   <http://example.org/>
    SELECT ?name ?mbox ?phone
    WHERE { ?p foaf:name ?name
            OPTIONAL { ?p foaf:mbox ?mbox }
            OPTIONAL { ?p ex:phone ?phone } }
    ORDER BY ?name
  $$);
--  name  |         contact
-- -------+--------------------------
--  Alice | mailto:alice@example.org
--  Bob   |
--  Carol | mailto:carol@example.org

See also

• MINUS — remove solutions that match a pattern.
• Not supported — the full list, with workarounds.