SQL

SQL postgres

Создание БД (две таблицы) с внешним ключом:

CREATE DATABASE cats_db;

CREATE TABLE shops
(
    id         BIGSERIAL PRIMARY KEY,
    shop       VARCHAR(99) NOT NULL
);

CREATE TABLE cats
(
    id         BIGSERIAL PRIMARY KEY,
    cat        VARCHAR(99) NOT NULL,
    id_shop    INTEGER NOT NULL DEFAULT 0,

    FOREIGN KEY (id_shop) REFERENCES shops(id) ON DELETE SET DEFAULT
);

Вставим значения в таблицы:

INSERT INTO shops VALUES (0, 'shop?');
INSERT INTO shops VALUES (DEFAULT, 'Pets I love');
INSERT INTO shops VALUES (DEFAULT, 'Pets are inside');
INSERT INTO shops VALUES (DEFAULT, 'Pets are here');

INSERT INTO cats VALUES (DEFAULT, 'Murzik', 1);
INSERT INTO cats VALUES (DEFAULT, 'Barsik', 2);
INSERT INTO cats VALUES (DEFAULT, 'Lapsik', 3);

Пример LEFT JOIN:

Запрос LEFT JOIN 2-х таблиц кошки и магазины:

SELECT cats.cat, shops.shop FROM cats LEFT JOIN shops ON cats.id_shop = shops.id;