Get records from table with join where records in join not contain specific value

I have two tables:

Table user:

create table user (
   id bigserial not null primary key,
   username varchar(256),
   active boolean not null default true
);

And table address:

create table address (
   id bigserial not null primary key,
   user_id integer not null,
   country varchar(256),
   city varchar(256),
   street varchar(256)
);

And some data as example:

insert into user(id, username, active) values (1, 'john', true);
insert into user(id, username, active) values (2, 'alex', true);
insert into user(id, username, active) values (3, 'alice', true);
insert into user(id, username, active) values (4, 'tom', true);
insert into user(id, username, active) values (5, 'dave', true);

insert into address(id, user_id, country, city, street) values (1, 1, 'Germany', 'Berlin', '');
insert into address(id, user_id, country, city, street) values (2, 2, 'Germany', 'Berlin', '');
insert into address(id, user_id, country, city, street) values (3, 2, 'Great Britain', 'London', '');
insert into address(id, user_id, country, city, street) values (4, 3, 'France', 'Paris', '');
insert into address(id, user_id, country, city, street) values (5, 4, 'USA', 'New York', '');
insert into address(id, user_id, country, city, street) values (6, 5, 'South Korea', 'Seoul', '');

Every user can have several addresses. I need to get all users who doesn’t have in their set of addresses address with specific country, for example ‘Germany’.

What I tried:

select u.* from user u
left join address a on u.id=a.user_id where a.country is not like '%Germany%'

But it returns users, who have address with specific country but also have some other address, which country is different from the specific one, for example with the data used above this is alex, who has two addresses Germany and Great Britain:

id  username  active
--------------------
2   alex      True
3   alice     True
4   tom       True
5   dave      True

Any suggestions how can I do such query?

Answer

Your code checks whether each user has at least one address outside of Germany, while you want to ensure that they have none.

I would recommend not exists:

select c.*
from client c
where not exists (
    select 1
    from address a 
    where a.user_id = c.id and a.country = 'Germany'
)

This query would take advantage of an index on address(user_id, country).

Note that it is unclear whether your table is called user or client… I used the latter.

Note that this also returns clients that have no address at all. If that’s not what you want, then an alternative uses aggregation:

select c.*
from client c
inner join address on a.user_id = c.id
group by c.id
having not bool_or(a.country = 'Germany')

Advertisement

Answer