[FIXED] How to differentiate between no rows and foreign key reference not existing?

Issue

My users table contains Alice, Bob and Charles. Alice and Bob have a 3 and 2 fruits respectively. Charles has none. A relationship is established using a foreign key constraint foreign key (user_id) references users (id) and a unique (user_id, name) constraint, allowing zero or one fruit per user.

create table users (
  id         integer primary key,
  firstname  varchar(64)
);

create table fruits (
  id          integer primary key not null,
  user_id     integer not null,
  name        varchar(64) not null,
  foreign key (user_id) references users (id),
  unique (user_id, name)
);

insert into users (id, firstname) values (1, 'Alice');
insert into users (id, firstname) values (2, 'Bob');
insert into users (id, firstname) values (3, 'Charles');

insert into fruits (id, user_id, name) values (1, 1, 'grape');
insert into fruits (id, user_id, name) values (2, 1, 'apple');
insert into fruits (id, user_id, name) values (3, 1, 'pear');
insert into fruits (id, user_id, name) values (4, 2, 'orange');
insert into fruits (id, user_id, name) values (5, 2, 'cherry');

Charles does not have an orange, so there is no resulting row (first query below). However, running the same query for a user that does not exist (second query below) also returns no result.

test=# select * from fruits where user_id = 3 and name = 'orange';
 id | user_id | name 
----+---------+------
(0 rows)

test=# select * from fruits where user_id = 99 and name = 'orange';
 id | user_id | name 
----+---------+------
(0 rows)

Is it possible to perform a single query whilst simultaneously differentiating between the user not existing vs the user existing and not having a fruit?

If so, can this also be done to find all the fruits belonging to a particular user (i.e. select * from fruits where user_id = 3 vs select * from fruits where user_id = 99.

Solution

Use a LEFT [OUTER] JOIN:

SELECT u.id, u.firstname, f.name AS fruit
--   , COALESCE(f.name, '') AS fruit  -- alternatively an empty string
FROM   users u
LEFT   JOIN fruits f ON f.user_id = u.id
                    AND f.name = 'orange'
WHERE  u.id = 3;

If the user exists, you always get a row.
If the user has no fruit (of that name), fruit defaults to NULL - which is otherwise impossible since fruit.name is defined NOT NULL.

To get an empty string instead, throw in COALESCE(). But no fruit can be named '' then or it'll be ambiguous again. Add a CHECK constraint to be sure.

Related:

• Postgres Left Join with where condition

Aside 1: "name" is not a good name.
Aside 2: Typically, this would be a many-to-many design with three tables: "users", "fruits" and "user_fruits". See:

• How to implement a many-to-many relationship in PostgreSQL?

Answered By - Erwin Brandstetter

Answer Checked By - Katrina (PHPFixing Volunteer)