Таблицы заказов, клиентов и продавцов SQL Natural Join

Question

Мне был задан следующий вопрос:

Напишите оператор SQL для объединения таблиц продавца, клиента и заказов в такой форме, чтобы один и тот же столбец каждой таблицы появлялся один раз и приходили только реляционные строки.

Я выполнил следующий запрос:

SELECT * FROM orders NATURAL JOIN customer NATURAL JOIN salesman;

Однако я не ожидал следующего результата:

Мое сомнение заключается в шаге 2.

Почему я не получил строки с salesman_id 5002, 5003 и 5007?

Я знаю, что естественное объединение использует общие столбцы для финализации строк.

Здесь все Salesman_ids присутствуют в результате шага 1.

Почему конечный результат не равен таблице, полученной на шаге 1, с добавлением к нему неповторяющихся столбцов продавца?

Answer 1

... один и тот же столбец каждой таблицы появится один раз

да Natural Join делает это

... и только реляционные строки придут.

Я не знаю, что это значит.

Я не согласен с теми, кто говорит: не используйте Natural Join, Но это правда, что если вы планируете использовать Natural Join для ваших запросов вы должны спроектировать схему так, чтобы (условно говоря) "одно и то же имя столбца означало одно и то же".

Затем это упражнение научит вас опасности иметь одноименные столбцы, которые не означают одно и то же. Опасность иногда называют "ловушкой соединения" или "ловушкой соединения". (На самом деле это не ловушка: вам просто нужно научиться писать запросы к плохо разработанным схемам.)

Более точный способ выразить это: если у вас есть столбцы с одинаковыми именами в двух разных таблицах, столбец должен быть ключом хотя бы одной из них. Так:

• city не является ключом ни в одной из этих таблиц, поэтому не должен быть "захвачен" в Natural Join,
• salesman_id не является ключом в таблице customer, поэтому не должен быть захвачен в соединении из таблицы orders,

Основной способ исправить этот запрос - переименовать некоторые столбцы, чтобы избежать "захвата" (см. Ниже). Также стоит упомянуть, что некоторые диалекты SQL позволяют:

SELECT *
FROM orders
NATURAL JOIN customer ON customer_id
...

ON column(s) Фраза означает: проверить, что единственными общими столбцами между двумя таблицами являются именованные. В противном случае отклоните запрос. Таким образом, ваш запрос будет отклонен.

Переименование означает, что вы не должны использовать SELECT *, (В любом случае, это опасно для "производственного кода", поскольку ваш запрос может создавать разные столбцы каждый раз, когда происходит изменение схемы.) Самый простой способ решить эту проблему может состоять в создании трех представлений для трех базовых таблиц с "случайным" одинаковым именованным столбцам дано другое имя. Для этого одного запроса:

SELECT ord_no, purch_amt, ord_date, customer_id,
       salesman_id AS order_salesman_id
FROM orders
NATURAL JOIN (SELECT customer_id, cust_name,
                     city AS cust_city, grade,
                     salesman_id AS cust_salesman_id
              FROM customer) AS customer_grr
NATURAL JOIN (SELECT salesman_id, name,
                     city AS salesman_city,
                     commission
              FROM salesman) AS salesman_grr

Я использую явное AS показать переименование. Большинство диалектов SQL позволяют вам пропустить это ключевое слово; просто положи city cust_city, ...,

Answer 2

Почему конечный результат не совпадает с таблицей, полученной на шаге 1 с [...]?

Потому что естественное соединение не работает так, как вы ожидаете - что бы это ни было, поскольку вы не говорите.

В терминах реляционной алгебры: естественное соединение возвращает строки
• чей набор столбцов является объединением входных наборов столбцов и
• которые имеют подстроку в обоих входах.

С точки зрения бизнеса: каждый результат таблицы и запроса содержит строки, которые превращают некоторый шаблон оператора - его (характерный) предикат - его "значение"- в истинное утверждение. Дизайнер дает предикаты для базовых таблиц. Здесь что-то вроде:

Orders = rows where
    order [ord_no] ... and was sold by salesman [salesman_id] to customer [customer_id] 
Customer = rows where
    customer [customer_id] has name [cust_name] and lives in city [city]
        and ... and is served by salesman [salesman_id]
Salesman = rows where
    salesman [salesman_id] has name [name] and works in city [city] ...

Естественное объединение определено так, что если каждый вход содержит строки, которые превращают его предикат в истинный оператор, то их естественное объединение содержит строки, которые превращают И / / соединение этих предикатов в истинный оператор. Итак (ваш запрос):

Orders natural join Customer natural join Salesman = rows where
    order [ord_no] ... and was sold by salesman [salesman_id] to customer [customer_id] 
and customer [customer_id] has name [cust_name] and lives in city [city]
        and ... and is served by salesman [salesman_id]
and salesman [salesman_id] has name [name] and works in city [city] ...

Таким образом, естественное объединение запрашивает строки, в которых, помимо прочего, клиент живет в городе, в котором работает продавец. Если это не то, что вам нужно, вам не следует использовать это выражение.

Обратите внимание, что значение естественного объединения таблиц является (простой) функцией значений входных таблиц. Это так для всех операторов отношений. Таким образом, каждое выражение запроса имеет значение, построенное из значений базовой таблицы и реляционных операторов.
Есть ли эмпирическое правило для построения SQL-запроса из понятного человеку описания?

Почему я не получил строки с salesman_id 5002, 5003 и 5007?

Потому что эти продавцы не работают в городе, в котором живет один из их клиентов.