I create two tables:

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 has a speciality_id foreign key. Adding entries:

 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 ('Игорев Игорь Игоревич');

We look (the indexes are different, but this is not the point):

 SELECT * FROM reader_speciality;

enter image description here

 SELECT * FROM readers;

enter image description here

Now I try to give the value of the foreign key instead of the index, and something goes wrong:

 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);

enter image description here

How to make a request in this situation?

    1 answer 1

    SQL always tries to select all records from the second table for each record from the first table. Usually we keep him from this desire by specifying the condition of the connection of two tables. But when OR readers.speciality_id IS NULL , the condition is true for any records from the second table.

    For such cases in SQL serves LEFT JOIN , which selects records if they are and returns a record from the first table even if there is nothing in the second.

     SELECT readers.reader_id, readers.fio, reader_speciality.value FROM readers LEFT JOIN reader_speciality ON readers.speciality_id = reader_speciality.speciality_id
    • Perfect. Thank. And how should the query be modified if, for example, there is a completely similar table reader_class and from there you need to take reader_class.value by class_id ? - InfernumDeus
    • one
      @InfernumDeus Add, after the above, another LEFT JOIN with its ON condition. - Mike