How to make left join with `where second_table.key!=’value’` if there is not record in secord_table at all

There are two tables with one-to-one relationship, and it’s a legal that the second table can have less rows than the first.

Here is a sql code with inserted data:

create table data(
    data_id int primary key,
    name text
);

create table data_extra(
    extra_id int references data(data_id) primary key ,
    extra text
);

insert into data values (1, 'n1');
insert into data values (2, 'n2');
insert into data values (3, 'n3');

insert into data_extra values (1, 'e1');
insert into data_extra values (2, 'e2');


select * from data left join data_extra on data_id = extra_id;
--  data_id | name | extra_id | extra
-- ---------+------+----------+-------
--        1 | n1   |        1 | e1
--        2 | n2   |        2 | e2
--        3 | n3   |          |
-- (3 rows)

I need to make a query for all data + data_extra tables where extra!=’e1′ Here is my try:

select * from data left join data_extra on data_id = extra_id
where extra != 'e1';
--  data_id | name | extra_id | extra
-- ---------+------+----------+-------
--        2 | n2   |        2 | e2
-- (1 row)

The problem with this query, that it doesn’t return data.name=’n3′ row, which doesn’t have a related row in data_extra table.

How can I fix it, to get as result data.name=n2 and data.name=n3.

Answer

You could do:

select * 
from data d
left join data_extra de on d.data_id = de.extra_id 
where de.extra_id is null or de.extra <> 'e1'

This removes rows that match and whose extra is equal to 'e1', while retaining non-matching rows.