Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Внешние обьединения  [new]
domanix
Member

Откуда: Санкт Петербург
Сообщений: 137
Привет вопрос чисто академический.
И так задача : есть 2 таблицы
t1 t2
--- ----
id id
--- ---
1 1
2 1
3 2
4 2
5 3

Нужно одним запросом получить ВСЕ записи из t1 и соответвующие им записи
из t2 - за исключением тех для которых t1.id=2 ..
т.е. если писать sql по стандарту то такое безобразие можно получить
следующим запросом
select t1.id, t2.id from t1 left join t2 on t1.id=t2.id and t1.id!=2;
Результат:

id | id
--- | ---
1 | 1
1 | 1
2 | null
3 | 3
4 | null
5 | null

ВОПРОC:
Как такой же результат получить используя оракловкий синтаксис внешнего обьединения "(+)"?
19 апр 06, 12:18    [2579077]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
arhey
Member

Откуда: Санкт-Петербург. Россия... :)
Сообщений: 221
with t1 as (select 1 as id from dual union all select 3 as id from dual)
, t2 as (select 1 as id from dual union all select 2 as id from dual union all select 1 as id from dual)
select *
from t1, t2
where t1.id = t2.id(+);
19 апр 06, 12:27    [2579147]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
Чисто академически предлагаю сравнить планы запросов....
19 апр 06, 12:38    [2579230]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Владимор Конев
Member

Откуда:
Сообщений: 3451
domanix
Привет вопрос чисто академический.
И так задача : есть 2 таблицы
t1 t2
--- ----
id id
--- ---
1 1
2 1
3 2
4 2
5 3

Нужно одним запросом получить ВСЕ записи из t1 и соответвующие им записи
из t2 - за исключением тех для которых t1.id=2 ..
т.е. если писать sql по стандарту то такое безобразие можно получить
следующим запросом
select t1.id, t2.id from t1 left join t2 on t1.id=t2.id and t1.id!=2;
Результат:

id | id
--- | ---
1 | 1
1 | 1
2 | null
3 | 3
4 | null
5 | null

ВОПРОC:
Как такой же результат получить используя оракловкий синтаксис внешнего обьединения "(+)"?
А собственно в чем проблема-то???
with 
  t1 as 
     (
        select 1 as num1 from dual union all
        select 2 as num1  from dual union all
        select 3 as num1  from dual union all
        select 4 as num1  from dual union all
        select 5 as num1  from dual ),
  t2 as 
     (
        select 1 as num2 from dual union all
        select 2 as num2  from dual union all
        select 3 as num2  from dual union all
        select 4 as num2  from dual
     )

select * 
  from t1,t2
 where num1 = num2(+)
   and num2(+) != 2

Query finished, retrieving results...

NUM1  NUM2
----  ----
   1     1
   3     3
   4     4
   5
   2

5 row(s) retrieved
19 апр 06, 12:49    [2579306]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116100
arhey
with t1 as (select 1 as id from dual union all select 3 as id from dual)
, t2 as (select 1 as id from dual union all select 2 as id from dual union all select 1 as id from dual)
select *
from t1, t2
where t1.id = t2.id(+);

Ну для академиков еще желательно
... and t1.id != 2
19 апр 06, 12:50    [2579314]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Elic
Member

Откуда:
Сообщений: 29976
dmidek
Ну для академиков еще желательно
... and t1.id != 2
Рано улыбаешься. Подумай тщательней :)
19 апр 06, 12:55    [2579359]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116100
Elic
dmidek
Ну для академиков еще желательно
... and t1.id != 2
Рано улыбаешься. Подумай тщательней :)

Да, да, я запутался в этих таблицах, сорри.
У Владимора Конева верно.
19 апр 06, 12:56    [2579371]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
domanix
Member

Откуда: Санкт Петербург
Сообщений: 137
>То Владимир Конев
>select *
> from t1,t2
> where num1 = num2(+)
> and num2(+) != 2

условие !=2 нужно наложить на ЛЕВУЮ(t1) таблицу!!!
Смотрите внимательно исходный запрос и задачу...

вопрос собственно в том чтобы без подзапросов повторить
результат
select t1.id, t2.id from t1 left join t2 on t1.id=t2.id and t1.id!=2;

но с синтаксисом (+); т.е. нужно
19 апр 06, 13:25    [2579590]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
domanix
Member

Откуда: Санкт Петербург
Сообщений: 137
вопрос собственно в том чтобы без подзапросов повторить
результат
select t1.id, t2.id from t1 left join t2 on t1.id=t2.id and t1.id!=2;

но с синтаксисом (+); т.е. нужно
наложить условие на внешнее левое обьединение двух таблиц
при этом в условии обьединения участвует правая таблица.
Причем поскольку это условие ОБЬЕДИНЕНИЯ - это условие никоим образом не ограничивает правую таблицу- не смотря на то что в условии фигурирует поле из правой же таблицы..
(ох - сумбурно так описал..)
я просто ни как не пойму как такое сделать с "(+)" без радикального усложнения запроса....
19 апр 06, 13:33    [2579643]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Elic
Member

Откуда:
Сообщений: 29976
domanix
условие !=2 нужно наложить на ЛЕВУЮ(t1) таблицу!!!
Смотрите внимательно исходный запрос и задачу...
Ты сперва попробуй. А потом подумай и выбери: чужая невнимательность или собственная непонятливость.
19 апр 06, 13:33    [2579644]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
domanix
Member

Откуда: Санкт Петербург
Сообщений: 137
>Ты сперва попробуй. А потом подумай и выбери: чужая невнимательность или собственная непонятливость.
Попробовал - результат не правильный..
мне нужно : where num1 = num2(+) and num1(+) != 2
а там
where num1 = num2(+) and num2(+) != 2

просто представьте себе что таблица t1 содержит не только полt ID а допустим
еще и другие поля - и условие обьединения будут накладываться именно на эти другие поля..
понятно же что я упростил ситуцию как мог...
19 апр 06, 13:45    [2579742]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116100
Elic
domanix
условие !=2 нужно наложить на ЛЕВУЮ(t1) таблицу!!!
Смотрите внимательно исходный запрос и задачу...
Ты сперва попробуй. А потом подумай и выбери: чужая невнимательность или собственная непонятливость.

2Elic - Я наверное сильно торомжу, но ума не приложу , почему зараза так работает ?

select t1.id, t2.id from t1 left join t2 on t1.id=t2.id and t2.id!=2;
все ясно нет проблем

но с какой стати это

select t1.id, t2.id from t1 left join t2 on t1.id=t2.id and t1.id!=2;

возвращает то же самое.
Я не ожидал в этом случае увидеть t1.id = 2 в полученном результате.

Направь пожалуйста, а то я грешным делом начинаю думать о багах ...
19 апр 06, 13:49    [2579764]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
domanix
Member

Откуда: Санкт Петербург
Сообщений: 137
Еще раз описываю задачу по другому
t1
------------
id | prefix
------------
1 | 'a'
2 | 'b'
3 | 'c'
----------


t2
-----------------
| id | sum|
|------------|
|1 | 10 |
|1 | 20 |
|2 | 30 |
|2 | 40 |
|3 | 50 |
|3 | 60 |

SELECT t1.prefix,t2.sum FROM t1 LEFT JOIN t1 ON t1.id=t2.id AND t1.prefix!='b';
результат:
'a' 10
'a' 20
'b' Null
'c' 50
'c' 60


ВОПРОC:
Как такой же результат получить используя оракловкий синтаксис внешнего обьединения "(+)" . Без радикального усложнения запроса.
19 апр 06, 13:56    [2579821]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Elic
Member

Откуда:
Сообщений: 29976
domanix
Elic
Ты сперва попробуй. А потом подумай и выбери: чужая невнимательность или собственная непонятливость.

Попробовал - результат не правильный..
Попробуй ещё раз. На "академическом" примере задачи - результат верный.
domanix
и условие обьединения будут накладываться именно на эти другие поля..
понятно же что я упростил ситуцию как мог...
Это тот случай, когда простота хуже воровства.
19 апр 06, 14:00    [2579860]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
domanix
Member

Откуда: Санкт Петербург
Сообщений: 137
dmidek
>возвращает то же самое.
>Я не ожидал в этом случае увидеть t1.id = 2 в полученном результате.

В этом то и соль всего вопроса!!
по стандарту условие записанное в outer join влияет только на
условие ОБЬЕДИНЕНИЯ выборки - но ни как не на ОГРАНИЧЕНИЕ.
т.е по правилам указанным в left или right join происходит обьединение
двух таблиц участвующих в обьединении - а вот то что в where - уже отсекает ненужные записи..
вместе с тем прямое обьединение Inner Join - практически эквивалентно
наложению условий в WHERE
Я привел пример - который это демонстрирует.
и стало мне интересно - как сделать тоже самое с (+) ( и возможно ли такое)
19 апр 06, 14:05    [2579907]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Elic
Member

Откуда:
Сообщений: 29976
domanix
Еще раз описываю задачу по другому
Как такой же результат получить используя оракловкий синтаксис внешнего обьединения "(+)" . Без радикального усложнения запроса.
select t1.prefix, t2.sum from t1, t2
  where t2.id(+) = decode(t1.prefix, 'b', to_number(null), t1.id);
19 апр 06, 14:10    [2579961]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Elic
Member

Откуда:
Сообщений: 29976
dmidek
on t1.id=t2.id and t2.id!=2;
on t1.id=t2.id and t1.id!=2;
возвращает то же самое.
Я не ожидал
Это же обычное условие, которое поддаётся обычным правилам эквивалентных преобразований. А с учётом левой части условия очевидна их эквивалентность.
Возможно "мешают" знания о (+) в части двусмысленности Null-а в выражениях с оператором outer-join-а. В on-кляузе такой двусмысленности просто нет.
IMHO.
19 апр 06, 14:21    [2580032]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116100
Elic
dmidek
on t1.id=t2.id and t2.id!=2;
on t1.id=t2.id and t1.id!=2;
возвращает то же самое.
Я не ожидал
Это же обычное условие, которое поддаётся обычным правилам эквивалентных преобразований. А с учётом левой части условия очевидна их эквивалентность.
Возможно "мешают" знания о (+) в части двусмысленности Null-а в выражениях с оператором outer-join-а. В on-кляузе такой двусмысленности просто нет.
IMHO.


Elic, большое спасибо. В совокупности с твоим примером из предыдущего поста стало понятнее.
ИМХО , пример с декодом показывает, что потенциал у ANSI- синтаксиса большой...
19 апр 06, 14:38    [2580158]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Elic
Member

Откуда:
Сообщений: 29976
dmidek

ИМХО , пример с декодом показывает, что потенциал у ANSI-синтаксиса большой...
Возможно. И даже наверняка. У него есть ниша для заполнения.
Но пусть пока реализаторы этого синтаксиса поработают над собой, а я (с учётом этого и других сопутствующих факторов) буду делать по надёжной старинке.
19 апр 06, 14:48    [2580233]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
SELECT t1.prefix, t2.summ
  FROM t1, t2
 WHERE t1.ID = t2.ID(+)
   AND t2.ID(+) <> 2

Вопрос концептуальный: кто правильней работает ANSI или (+)

Делая t1.prefix != 'b' вы налагаете ограничение на исходную таблицу
И это есть правильно! Т.к. если вам не нужны эти записи - вы их просто выбрасываете ...

И оффтоппп.... но какой!!!

Стишок!

Кто возьмется написать Возвышенное Стихотворение, где эти
строки были бы либо рефреном, либо последними?
"Меня интерисуют только мыши
их стоимость и где преобрести"


Однажды я в умат, до сноса крыши,
До рвоты накурился анаши.
Теперь интересуют только мыши,
Их стоимость, и где приобрести...
---
Потуплен взор, ланиты жаром пышут,
Дрожат ресницы мотыльком в горсти,
Полночный бриз златую прядь колышет,
И страстный вздох из уст твоих летит...

О, кто в словах красу твою опишет?
Ты бесподобна, друг мой, но... прости,
Меня интересуют только мыши,
Их стоимость, и где приобрести.

19 апр 06, 15:28    [2580542]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
Elic
Member

Откуда:
Сообщений: 29976
Alexey Polovinkin
Вопрос концептуальный: кто правильней работает ANSI или (+)
Почитай на форуме про первый во всей некрасе и узнаешь :)
Alexey Polovinkin
Делая t1.prefix != 'b' вы налагаете ограничение на исходную таблицу
И это есть правильно! Т.к. если вам не нужны эти записи - вы их просто выбрасываете ...
То, что это понятней и естественней, это беспорно. Но не более.
19 апр 06, 15:45    [2580653]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
domanix
Member

Откуда: Санкт Петербург
Сообщений: 137
Alexey Polovinkin
Вопрос концептуальный: кто правильней работает ANSI или (+)

Да нет меня не интересует кто правильней работает..
У меня совсем другие мотивы...

Alexey Polovinkin

Делая t1.prefix != 'b' вы налагаете ограничение на исходную таблицу
И это есть правильно! Т.к. если вам не нужны эти записи - вы их просто выбрасываете ...

В том то и дело что НЕТ в данном случае - t1.prefix != 'b' налагает ограничение на обьединение, но ни как не на исходную таблицу ( все правильно с точки зрения ansi ( тут просто сложно вьехать что условие обьединения и ограничение результирующего набора - разные вещи).
И с точки зрения той задачи которую я привел - мне нужно получить
ВСЕ записи из исходной талицы ( не выбрасывая ни одной) и только те записи в левой таблице для которых условие приведенной в on кляузе верно..
Допустим для последующего агрегирования ( например это были манагеры и их продажи и мне нужно получить ВЕСЬ список манагеров с суммой их продаж при этом не показывая продажи некоторых манагеров.(Естественно этот пример притянут за уши- ибо сию задачу можно решить и другими методами - но меня интересовал именно такой подход. И именно по этому я и назвал вопрос академическим)))
Лично меня ввело в ступор как это сделать с (+).
Но ответ таки я получил. Спасибо...
19 апр 06, 15:51    [2580719]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116100
Alexey Polovinkin
SELECT t1.prefix, t2.summ
  FROM t1, t2
 WHERE t1.ID = t2.ID(+)
   AND t2.ID(+) <> 2

ИМХО, пример не из той области ...

Alexey Polovinkin

Вопрос концептуальный: кто правильней работает ANSI или (+)

Делая t1.prefix != 'b' вы налагаете ограничение на исходную таблицу
И это есть правильно! Т.к. если вам не нужны эти записи - вы их просто выбрасываете ...



ANSI - синтаксис предполагает и такой вариант

Сравните

SELECT t1.prefix,t2.sum FROM t1 LEFT JOIN t1 ON t1.id=t2.id 
WHERE t1.prefix!='b'

SELECT t1.prefix,t2.sum FROM t1 LEFT JOIN t1 ON t1.id=t2.id 
AND t1.prefix!='b'

Как Вы видите, они не эквивалентны, иначе бы синтаксис был тавтологичен.

Но ИМХО, если много об этом думать, с непривычки начинает болеть голова :-))
19 апр 06, 15:54    [2580743]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
domanix
Member

Откуда: Санкт Петербург
Сообщений: 137
автор
Почитай на форуме про первый во всей некрасе и узнаешь :)

Кстати - можеьт кто даст прямые ссылки - на список проблем с ANSI синтаксисом в ORACLE. Я по форуму ищу и нгайти не могу..
Я человек в Оракле новый и мне нужно знать где стоят грабли.( ибо по привычке пишу sql по ansi стандартам)
19 апр 06, 15:55    [2580751]     Ответить | Цитировать Сообщить модератору
 Re: Внешние обьединения  [new]
arhey
Member

Откуда: Санкт-Петербург. Россия... :)
Сообщений: 221
dmidek
arhey
with t1 as (select 1 as id from dual union all select 3 as id from dual)
, t2 as (select 1 as id from dual union all select 2 as id from dual union all select 1 as id from dual)
select *
from t1, t2
where t1.id = t2.id(+);

Ну для академиков еще желательно
... and t1.id != 2


согласен. поспешил.
- балл с меня :).
19 апр 06, 16:18    [2580878]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить