How to differentiate between empty result set versus non existent foreign key relation?

Question

In my application projects has a one-to-many relationship with widgets. Each project contains many widgets.

Within my application I key a widget as using composite key (code, slug), i.e. (p1, w2) and (p2, w2) are distinct. Project p1 has its own widget w2 and project p2 has a different widget w2 with the same code. This provides separate scope for project owners.

I want a query that will get a widget by composite key, and at the same time distinguish whether the projects.slug does not match versus the widgets.code not matching.

Here's what I've tried so far. The WHERE clause filters for a specific project slug and widget code but isn't able to determine if the projects slug is missing or the widgets code is missing.

The first query below returns an empty result because there is no w2 widget for project p3. However, the second query below returns also an empty result set, but for a different reason; the project slug does not exist in the projects table.

test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'p3' AND code = 'w2';
id | slug | id | code 
----+------+----+------
(0 rows)

test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'missing-project' AND code = 'w2';
 id | slug | id | code 
----+------+----+------
(0 rows)

What query will yield a result that can differentiate a non-existent project slug, an existent project with non-existent widget code and a complete match for both project slug and widget code?

---

Background to this question:

I've learned how to do this using a query to fetch all widgets for a project. (How to differentiate between no rows and foreign key reference not existing?)

If I use a query with only the slug I detect when a project is missing (empty result set - 2nd statement below) versus the project being there but having no widgets (1 row with empty id column - 3rd statement below).

test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id 
WHERE p.slug = 'p1';
 id | slug | id | code 
----+------+----------+----------
  1 | p1   | 51 | w1
  1 | p1   | 52 | w2
(2 rows)


test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id 
WHERE p.slug = 'p3';
 id | slug | id | code 
----+------+----+------
  3 | p3   |  0 | 
(1 row)


test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id 
WHERE p.slug = 'missing-project';
 id | slug | id | code 
----+------+----+------
(0 rows)

SELECT
  p.id, p.slug, COALESCE(w.id, 0), COALESCE(w.code, '')
FROM projects p
LEFT OUTER JOIN widgets w
  ON p.id = w.project_id
WHERE p.slug = 'p4';

CREATE TABLE projects (
  id    INTEGER,
  slug  VARCHAR(32),
  PRIMARY KEY (id)
);

CREATE TABLE widgets (
  id          INTEGER NOT NULL,
  project_id  INTEGER NOT NULL,
  code        VARCHAR(32) NOT NULL,
  PRIMARY KEY (id, project_id),

  CONSTRAINT widgets_project_id_fkey FOREIGN KEY (project_id)
     REFERENCES projects (id)
);

INSERT INTO projects (id, slug) VALUES (1, 'p1');
INSERT INTO projects (id, slug) VALUES (2, 'p2');
INSERT INTO projects (id, slug) values (3, 'p3');

INSERT INTO widgets (id, project_id, code) VALUES (51, 1, 'w1');
INSERT INTO widgets (id, project_id, code) VALUES (52, 1, 'w2');
INSERT INTO widgets (id, project_id, code) VALUES (53, 2, 'w2');

Answer 1

If I understand well there are three cases you want to differentiate:

1. The project and widget exist.
2. The project exists but the widget doesn't.
3. The project does not exist.

If that's a correct description of the cases, you can place the widget search in the ON clause of the join, and place the project search in the WHERE clause of the query.

For example:

SELECT
  p.id, 
  p.slug, 
  COALESCE(w.id, 0) as id, 
  COALESCE(w.code, '') as code
FROM projects p 
LEFT OUTER JOIN widgets w ON p.id = w.project_id 
                         AND w.code = 'w1' -- widget code
where p.slug = 'p3' -- project code

Case #1 Result:

id  slug  id  code
--  ----  --  ----
 1  p3    10  w1

Case #2 Result:

id  slug  id  code
--  ----  --  ----
 1  p3    10  <null>

Case #3 Result:

id  slug  id  code
--  ----  --  ----
-- 0 rows returned

Though it still returns a row, case #2 can be differentiated from case #1 by the code column showing up as null. Case #3 does not return any row at all.

See running example at db<>fiddle.

The problem with your original approach was that the WHERE condition w.code = 'w2' effectively turned your left outer join into an inner join: any result that had NULLs on the widget side would be eliminated by that condition. That's why you never got these results. If you put that condition in the join condition instead, it will not eliminate such results. The reason for that is the way left outer joins are defined: they are the result of the inner join plus and row from the left side that is missing in the result, supplemented with NULLs on the right side. Your condition would have eliminated the row from the inner join, so it appears in the result with NULLs on the widget side.