Select таблицы с внешним ключем, который может иметь значение NULL

#sql #postgresql #select

Создаю две таблицы:

CREATE TABLE public.reader_speciality (
  speciality_id SMALLSERIAL,
  value text,
  CONSTRAINT reader_speciality_pkey PRIMARY KEY (speciality_id)
) WITH ( OIDS=FALSE );
ALTER TABLE public.reader_speciality
  OWNER TO ""{userID}"";

CREATE TABLE public.readers(
  reader_id SERIAL NOT NULL,
  fio text NOT NULL,
  speciality_id smallint,
  CONSTRAINT readers_speciality_id_fkey FOREIGN KEY(speciality_id)
    REFERENCES public.reader_speciality(speciality_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
) WITH(OIDS= FALSE);
ALTER TABLE public.readers
  OWNER TO ""{userID}"";


readers имеет внешний ключ speciality_id. Добавляю записи:

INSERT INTO reader_speciality (value) VALUES ('Физика');
INSERT INTO reader_speciality (value) VALUES ('Химия');
INSERT INTO readers (fio, speciality_id) VALUES ('Иванов Иван Иванович', 1);
INSERT INTO readers (fio) VALUES ('Игорев Игорь Игоревич');


Смотрим (индексы другие, но это не суть):

SELECT * FROM reader_speciality;




SELECT * FROM readers;




Теперь пробую выдать вместо индекса значение внешнего ключа и что-то идёт не так:

SELECT readers.reader_id,
       readers.fio,
       reader_speciality.value
FROM   readers, reader_speciality
WHERE (readers.speciality_id = reader_speciality.speciality_id OR
       readers.speciality_id IS NULL);




Как правильно составить запрос в такой ситуации?
    

Ответы

Ответ 1

SQL всегда пытается для каждой записи из первой таблицы подобрать все записи из второй
таблицы. Обычно мы удерживаем его от этого желания задав условие связи двух таблиц.
А вот когда срабатывает OR readers.speciality_id IS NULL получается, что условие истинно
для любых записей из второй таблицы.

Для таких случаев в SQL служит LEFT JOIN, который подбирает записи если они есть
и возвращает запись из первой таблицы даже если во второй ничего нет.

SELECT readers.reader_id,
       readers.fio,
       reader_speciality.value
  FROM readers
  LEFT JOIN reader_speciality
    ON readers.speciality_id = reader_speciality.speciality_id