Разница между объединением с WHERE AND JOIN в предложении SELECT
У меня есть два запроса:
select id,(SELECT N.NAME
FROM memuat.NETWORK N
join memuat.host H
on N.ID = H.NETWORK ) as network_name
from memuat.host;
select id,(SELECT N.NAME
FROM memuat.NETWORK N
where N.ID = H.NETWORK ) as network_name
from memuat.host H;
Первый возвращает:
ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
второй работает нормально и возвращает данные. Мне ясно, что первый возвращает более одной строки, потому что я могу запустить подзапрос один и проверить это. Что мне непонятно, так это то, как второй запрос может вернуть только одну строку? Мой результат - это не одна строка, а несколько строк.
хорошо, но что делать? он возвращает одну строку?
Для каждой строки, выбранной из H, выполняется подзапрос. И оценивается условие N.ID = H.NETWORK в подзапросе.
но это будет работать только в том случае, если N.ID является уникальным индексом, верно?
Если в N есть две (или более) строки, удовлетворяющие условию, вы получите ошибку «ORA-01427: однострочный подзапрос возвращает более одной строки».
... И в этом случае столбец не должен называться идентификатором, поскольку идентификатор таблицы должен однозначно идентифицировать строку.
Ответы 1
В обоих запросах подзапрос выполняется один раз для каждой строки хоста.
Во втором запросе подзапрос:
select n.name from memuat.network n where n.id = h.network
Это получает сетевое имя для сетевого идентификатора хоста.
В вашем первом запросе подзапрос:
SELECT n.name
FROM memuat.network n
JOIN memuat.host h ON n.id = h.network
Это получает список имен сетей. Вероятно, с большим количеством дубликатов, потому что мы получаем одну строку на хост.
Основной запрос в обоих случаях:
SELECT id, ( <subquery> ) AS network_name
FROM memuat.host h;
Здесь предполагается, что подзапрос возвращает одно значение, а именно сетевое имя хоста. Поскольку подзапрос во втором запросе возвращает одну строку с одним значением, это работает. Поскольку подзапрос в вашем первом запросе возвращает много строк, вы получаете сообщение об ошибке.
хорошо понял. так что речь идет не об использовании where или join, а о том, чтобы ссылаться на внешний запрос во внутреннем или нет? или в этом случае можно построить запрос, который ссылается на внешний запрос с оператором where? Я попытался сослаться на внешний запрос с помощью join, но мне это не удалось.
Во втором запросе внутренний запрос имеет ссылку на строку в основном запросе (h.network). Это называется коррелированным подзапросом. В первом запросе такой ссылки нет. Это автономный подзапрос, также называемый некоррелированным подзапросом. Отвечает ли это на ваш вопрос?
Я хотел бы на самом деле знать, могу ли я изменить первый запрос, чтобы добиться того же, что и во втором запросе, но с сохранением ключевого слова join в выражении. это возможно сделать?
Да и смысла в этом просто не было бы. Подзапрос получает слишком много строк, потому что он выбирает сетевые имена для всех хостов вместо одного хоста в основном запросе. С помощью where host.id = h.id вы бы выбрали ту же самую строку хоста в подзапросе, которая уже есть в основном запросе (при условии, что эта таблица имеет уникальный идентификатор). Вы даже можете использовать для этого адрес строки: where host.rowid = h.rowid.
Второй запрос имеет коррелированный подзапрос. т.е. у него есть условие, ссылающееся на основную таблицу запросов H.