Znajdź identyfikatory z listy, która nie istnieje w tabeli

Powiedz, że mam następujący schemat i dane:

create table images(
  id int not null
);

insert into images values(1), (2), (3), (4), (6), (8);

Chcę wykonać zapytanie takie jak:

select id from images where id not exists in(4, 5, 6);

Ale to nie działa. Powyższy przypadek powinien zostać zwrócony 5, ponieważ nie istnieje on w rekordach tabeli.

Możesz użyć sprzężenia zewnętrznego z valueslistą (podobną do wspomnianej powyżej odpowiedzi Martina):

select t.id
from (
  values (4),(5),(6) 
) as t(id)
  left join images i on i.id = t.id
where i.id is null;

lub a not existswraz z konstruktorem wierszy:

select *
from ( 
   values (4),(5),(6)
) as v(id)
where not exists (select *
                  from images i
                  where i.id = v.id);

Jeśli chcesz, możesz również umieścić valuesklauzulę w CTE, aby ułatwić odczytanie ostatniego zapytania:

with v (id) as (
 values (4),(5),(6)
)
select v.id
from v
  left join images i on i.id = v.id
where i.id is null;

Jednym ze sposobów na zrobienie tego byłoby VALUESutworzenie wyrażenia tabelowego z identyfikatorami w celu sprawdzenia i EXCEPTznalezienia brakujących.

SELECT id
FROM (VALUES(4),(5),(6)) V(id)
EXCEPT
SELECT id 
FROM images;

Podczas korzystania EXCEPTjak @Martin warunkiem , należy pamiętać, aby zrobić to EXCEPTALL, chyba, że chcesz zapłacić trochę więcej za starając się złożyć duplikatów.

BTW, VALUESwyrażenie może stać samo w sobie:

VALUES (4),(5),(6)
EXCEPT ALL
SELECT id FROM images;

Ale w ten sposób otrzymujesz domyślne nazwy kolumn.

W przypadku długiej listy wartości wygodniej jest podać ją jako tablicę i nieokreśloną. Krótsza składnia:

SELECT * FROM unnest('{4,5,6}'::int[]) id
EXCEPT ALL
SELECT id FROM images;

Istnieje kilka podstawowych technik zadania:

• Wybierz wiersze, których nie ma w innej tabeli

Po prostu użyj drugiego stołu i dołącz do nich.

create table images1(
  id int not null
);

create table images2(
  id int not null
);

insert into images1 values(1), (2), (3), (4), (6), (8);

insert into images2 values (4), (5), (6);

SELECT i2.ID

FROM images2 i2

LEFT JOIN images1 i1
    ON i1.ID = i2.ID

WHERE i1.ID IS NULL