Полное внешнее соединение Oracle с фильтром (ANSI) не работает должным образом

Я столкнулся с проблемой, когда полное внешнее соединение Oracle ANSI с фильтром не возвращает результаты, как я их ожидаю. Я создал быстрый пример, чтобы объяснить, что я делаю и вижу...

Table 1 - MUPPET

ID NAME 
1 Kermit the Frog 
2 Fozzie Bear 
3 Mrs. Piggy 
4 Beaker 
5 Animal 
6 Swedish Chef

Table 2 - PHONE

ID MUPPET_ID PHONE VALID
1 1 1111111111 Y
2 1 2222222222 N
3 2 3333333333 Y
4 4 4444444444 Y
5 5 5555555555 Y
6 6 6666666666 Y
7 6 7777777777 N
8 8 8888888888 Y

Из этих таблиц я хочу выбрать все маппеты и все действительные номера телефонов. Я хочу, чтобы все маппеты имели или нет номер телефона, и я хочу выбрать все действующие телефонные номера независимо от того, связаны они или нет с muppet. Это запрос, который я ожидаю работать...

SELECT m.id muppet_id,
 m.name,
 p.id phone_id,
 p.phone,
 p.valid
 FROM muppet m
 FULL OUTER JOIN
 phone p
 ON (M.ID = P.MUPPET_ID AND P.VALID = 'Y')

Но вот результаты, которые включают недействительные телефоны, хотя я не указал недействительные телефоны во внешнем фильтре присоединения

MUPPET_ID NAME PHONE_ID PHONE VALID

1 Kermit the Frog 1 1111111111 Y
 2 2222222222 N
2 Fozzie Bear 3 3333333333 Y
4 Beaker 4 4444444444 Y
5 Animal 5 5555555555 Y
6 Swedish Chef 6 6666666666 Y
 7 7777777777 N
 8 8888888888 Y
3 Mrs. Piggy

Я смог наконец получить результаты, которые я искал, используя подвыбор в левой части полного внешнего соединения

SELECT m.id muppet_id,
 m.name,
 p.id phone_id,
 p.phone,
 p.valid
 FROM muppet m
 FULL OUTER JOIN
 (SELECT id,
 phone,
 valid,
 muppet_id
 FROM phone
 WHERE valid = 'Y') p
 ON (M.ID = P.MUPPET_ID)

... и результаты...

MUPPET_ID NAME PHONE_ID PHONE VALID

1 Kermit the Frog 1 1111111111 Y
2 Fozzie Bear 3 3333333333 Y
4 Beaker 4 4444444444 Y
5 Animal 5 5555555555 Y
6 Swedish Chef 6 6666666666 Y
 8 8888888888 Y
3 Mrs. Piggy

Но я не понимаю, почему я должен был бы запросить этот путь. Может кто-то, пожалуйста, объясните мне, почему мой первоначальный внешний запрос на соединение с фильтром не работает должным образом?

РЕДАКТИРОВАТЬ:

Еще интереснее. Когда я запускаю этот запрос, я получаю 6 записей, как ожидалось

select valid from (
SELECT m.id muppet_id,
 m.name,
 p.id phone_id,
 p.phone,
 p.valid
 FROM muppet m
 FULL OUTER JOIN
 phone p
 ON (M.ID = P.MUPPET_ID AND P.VALID = 'Y')
) where valid = 'Y'

НО, когда я запускаю это, я не получаю никаких возвращенных записей

select valid from (
SELECT m.id muppet_id,
 m.name,
 p.id phone_id,
 p.phone,
 p.valid
 FROM muppet m
 FULL OUTER JOIN
 phone p
 ON (M.ID = P.MUPPET_ID AND P.VALID = 'Y')
) where valid <> 'Y'

Может быть, это проблема с оптимизатором или драйвером Oracle?

2 ответа

Вероятно, вы должны использовать левое внешнее соединение вместо полного внешнего соединения.

В полном внешнем соединении вы соединяетесь слева направо на основе m.id и p.valid, но вы также возвращаете все строки справа, которые не присоединяются ни к чему, а все строки с левой стороны, которые не присоединяются на что угодно.


Как пояснили в комментариях другие, это связано с тем, где вы помещаете логику фильтра. Таким образом, ваш исходный запрос вернет все маппеты и все номера телефонов и покажет их как объединенные, когда идентификаторы совпадут И действительны = 'Y'. Вот почему вы видите все номера телефонов, но только для действительных.

Вы можете либо сделать подход, который вы уже выяснили, либо переместить "действительную" логику в предложение WHERE:

SELECT m.id muppet_id,
 m.name,
 p.id phone_id,
 p.phone,
 p.valid
FROM muppet m
 FULL OUTER JOIN
 phone p
 ON (M.ID = P.MUPPET_ID)
WHERE
 P.VALID = 'Y' or P.MUPPET_ID is null;

Теперь предложение where отбрасывает строки, где VALID <> 'Y' или где не было соответствия для телефонной таблицы. Если вы сделали предложение where только "P.VALID = 'Y", тогда вы бы превратили это в правое внешнее соединение.

Тем не менее, я бы выбрал вторую версию, которую вы указали, поскольку это позволит вам использовать индекс VALID = 'Y', если этот индекс существует, где указанная выше версия, скорее всего, не будет использовать индекс из-за OR в том месте пункт.

licensed under cc by-sa 3.0 with attribution.