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.
CodePudding user response：
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:
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: