применение full join

← →
_drug_ (2009-07-24 07:48) [0]

Есть две таблицы:
create table first_table (
id integer not null,
data1 somedatatype)

create table second_table(
id integer not null,
data2 somedatatype)

id есть PK в обоих таблицах, data1 и data2 собс-но данные. При этом записи с каким-либо id могут быть в first_table и отсутствовать в second_table и наоборот. Нужно объеденить эти таблицы по первичному ключу и я использовал full join:
select coalesce(f.id, s.id), f.data1, s.data2
from first_table f full join second_table s on f.id = s.id

В целом, меня результат устраивает, но хотелось бы знать мнение более опытных товарищей о правильности использования full join"а в данном случае и вообще, а также есть ли ему альтернативы.
На подробностях не настаиваю, лаконичного ответа будет достаточно.

← →
StriderMan (2009-07-24 12:43) [1]

запрос сам себе противоречит:
...coalesce(f.id, s.id)

...on f.id = s.id

может нужен просто UNION
SELECT id, data from first_table
UNION
SELECT id, data from second_table

не?

← →
topS (2009-07-24 16:12) [2]

для соединения (а не объединения) таблиц существует JOIN, подробнейшим образом расписанный в любой даже самой примитивной документации

в чем проблема?

← →
_drug_ (2009-07-24 18:08) [3]

> StriderMan (24.07.09 12:43) [1]
> запрос сам себе противоречит:...coalesce(f.id, s.id)...on
> f.id = s.idможет нужен просто UNIONSELECT id, data from
> first_tableUNIONSELECT id, data from second_tableне?<Цит

Нет. Возможны случаи, когда либо f.id, либо s.id равен null, поэтому и заюзал full join. А data1 и data2 хранят разные данные.

← →
_drug_ (2009-07-24 18:09) [4]

> topS (24.07.09 16:12) [2]
> для соединения (а не объединения) таблиц существует JOIN,
> подробнейшим образом расписанный в любой даже самой примитивной
> документациив чем проблема?

Меня интересует правильное решение я выбрал или есть более лучшие решения проблемы.

← →
turbouser___ (2009-07-24 18:41) [5]

> _drug_ (24.07.09 18:09) [4]

Решение нормальное.

← →
topS (2009-07-24 19:22) [6]

> _drug_ (24.07.09 18:09) [4]
> более лучшие решения проблемы.

какой проблемы если не секрет?

← →
Виталий Панасенко(дом) (2009-07-25 10:40) [7]

а все-таки прикольно узнать, зачем все это?

← →
_drug_ (2009-07-27 07:21) [8]

> topS
> какой проблемы если не секрет?

соединения таблиц

> Виталий Панасенко(дом) (25.07.09 10:40) [7]
> а все-таки прикольно узнать, зачем все это?

Краткий экскурс в историю вопроса. Программирование мое хобби, последние 5 лет подзабросил. Теперь на работе по личной инициативе решил автоматизировать некоторые задачи, в ходе чего возникли вопросы, спросить не у кого, поэтому спрашиваю здесь. В частности про full join - мне показалось, что это слишком простое решение "в лоб", за простоту которого приходится платить нагрузкой на сервер и я решил это уточнить. Я ответил на вопрос?

← →
Виталий Панасенко (2009-07-27 09:37) [9]

Нет...имелось ввиду, что нужно получить.. я так и не понял. честно...

← →
_drug_ (2009-07-27 11:36) [10]

> Виталий Панасенко

Есть две таблицы, в одной фио и вес человека, в другой фио и его рост. причем для кого-то есть и рост и вес, для других что-то одно. нужно объеденить данные в одну таблицу, где и фио, и рост, и вес.
таблица рост:
фио рост
вася 166
петя 180
коля 175

таблица вес:
фио вес
ваня 55
вася 53
сережа 90

в итоге нужно получить:
фио вес рост
вася 53 166
ваня 55 null
петя null 180
коля null 175
сережа 90 null

inner join даст только
фио вес рост
вася 53 166

left и right join"ы тоже не то. остался full join, но большая нагрузка на сервер и я спросил здесь совета.

← →
Anatoly Podgoretsky © (2009-07-27 11:48) [11]

> _drug_ (27.07.2009 11:36:10) [10]

Неправильная структура, до нормализовались, 50 нормальной формы.

← →
Виталий Панасенко (2009-07-27 12:21) [12]

а на кой вес, характеризующий Васю лепить в от дельную таблицу?8-0. я б еще понял адрес, тогда для всех, проживающих по одному адресу указываем ИД адреса, а сам адрес - в отдельной(ых) таблице(ах)... а это - явный перебор...

← →
_drug_ (2009-07-27 14:30) [13]

> Anatoly Podgoretsky

Анатолий, интуитивно догадываюсь, что вы хотели сказать, но толком не понял. :-)

> Виталий Панасенко

а потому, что эти таблицы на самом деле view, в которых селектится куча таблиц, в силу нормализованности базы + некоторой неоптимальности структуры. и потом эти вьюхи объединяются в одну как раз по ФИО. единственно, что я смог улучшить, это за основу использовал таблицу со всеми ФИО, а потом левым джойном к ней прилепил эти вьюхи. не уверен, что комильфо, но это лучше. а если по уму, то надо переделать вьюхи, а это уже другая ветка будет, по-моему.
В общем, от full join"а я отказался, хотя вопрос остается, только по другому сформулирую - кто-то может привести пример, когда без full join не обойтись? или этот элемент SQL никогда не используется?

← →
Anatoly Podgoretsky © (2009-07-27 14:59) [14]

> _drug_ (27.07.2009 14:30:13) [13]

Еще раз, не надо отдельных таблиц, для однострочных данных, они должны составлять единое целое - Вася 53 166

← →
Виталий Панасенко (2009-07-27 16:32) [15]

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

← →
_drug_ (2009-07-28 08:11) [16]

> Виталий Панасенко

Виталий, вот структура :-)

id, string50, string180, money, anumberof есть домены.

Дана таблица описывающая набор однотипных предметов.
CREATE TABLE TITEM ( ITEM_ID INTEGER NOT NULL, MODEL STRING50, COMMENTS STRING180);

над каждым предметом может быть произведена одна или несколько процедур
CREATE TABLE TPROC_TYPE ( ID ID NOT NULL, NAME STRING50 NOT NULL);

какая процедура над каким предметом проводятся и сколько она стоит хранится здесь (стоимость здесь, правда, избыточна, она считается на основе стоимости операций, входящих в состав процедуры)
CREATE TABLE TPROC_LIST ( ID ID NOT NULL, ITEM_ID ID NOT NULL, PROCEDURE_ID ID NOT NULL, COST MONEY NOT NULL);

в состав процедуры могут входить только определенные операции из данной таблицы
CREATE TABLE TOPERATIONS ( ID ID NOT NULL, NAME STRING120 NOT NULL, COST MONEY NOT NULL);

содержание каждой процедуры хранится здесь
CREATE TABLE TPROC_CONTENT ( ID ID NOT NULL, PROC_ID ID NOT NULL, --ид процедуры OPERATION_ID ID NOT NULL, --ид операции AMOUNT ANUMBEROF NOT NULL, --количество операций в процедуре PROC_TYPE ID NOT NULL); --тип процедуры, здесь ошибка, он должен быть в таблице TPROC_LIST, но что имеем

нужен запрос, возвращающий
item_id, model, comments, cost1, cost2, cost3 ... costn
где costn - стоимость процедуры n-го типа над данным предметом. это в общем случае, у меня задача
полегче - всего 4 типа процедур, значит нужно определить 4 стоимости.

Я создал 4 представления по типу:

CREATE VIEW COST1( ITEM_ID, COST) AS select i.ITEM_ID item_id , sum(pc.amount*o.cost) cost from titem i , toperations o , tproc_list pl , tproc_type pt , tproc_content pc where pl.item_id = i.item_id --связываем список процедур со списком предметов and pl.procedure_id = pc.proc_id --связываем список процедур с их содержанием and o.id = pc.operation_id --связываем содержание процедур с операциями and pt.id = pc.proc_type --связываем тип процедуры с содержанием (здесь ошибка в структуре) and pc.proc_type = 1 --выбираем жестко прошитый тип процедуры group by i.item_id; группируем, чтобы подсчитать сумму

Каждое представление возвращает item_id, cost. Дальше 4 представления нужно объеденить и здесь я пришел к full join (т.к. не для каждого предмета все процедуры заданы), а затем к left join.
select i.item_id , c1.cost cost1 , c2.cost cost2 , c3.cost cost3 , c4.cost cost4 from titem i left join cost1 c1 on i.id = c1.item_id left join cost2 c2 on i.id = c2.item_id left join cost3 c3 on i.id = c3.item_id left join cost4 c4 on i.id = c4.item_id

Если кто подскажет, как объеденить мне эти представления в один запрос, буду признателен.

← →
Виталий Панасенко (2009-07-28 12:20) [17]

для "скалы" не скажу, но для ЖарПтицы реально примерно так
execute block
returns
(item_id bigint, model varchar(50),
comments varchar(180),
cost1 numeric(18,2),
cost2 numeric(18,2),
cost3 numeric(18,2),
cost4 numeric(18,2)
as
declare variable i integer;
cost numeric(18,2);
begin
for
select item_id, model, comments from titem
into :item_id, :model, :comments do
begin
I = 1;
for
select first 4 -- Выбираем гарантировано не более 4 типов процедур
cost from tproc_list order by procedure_id
into :cost
do
begin
if (I=1) then cost1 = :cost;
if (I=2) then cost2 = :cost;
if (I=3) then cost3 = :cost;
if (I=4) then cost4 = :cost;
I = I + 1;
suspend;
end
end
end;

← →
_drug_ (2009-07-28 13:09) [18]

> Виталий Панасенко

у меня ЖарПтица 1_5, и execute block не поддерживается ((
придется делать процедуру.
С таким подходом нужно будет триггеры делать на обновление поля cost в tproc_list, но это нормально. но вот связь между значением I и реальным типом процедуры косвенная, боюсь, что к модификации этот код будет очень чувствителен. но подход для меня интересный. и еще я подумал - код работать не будет, нужно select first 4 делать из tproc_type... но я понял, куда можно копать - в сторону ХП. ))) зачем извращаться с селектами, если можно сделать на ХП?

← →
Виталий Панасенко (2009-07-28 14:03) [19]

спросонья подумал, что СУБД - MS SQL... а на счет
> и еще я подумал - код работать не будет, нужно select first
> 4 делать из tproc_type

интересно, как тогда связать TPROC_TYPE с TITEM?!!! связь есть только в TPROC_LIST. можно, конечно, связать эти 3 таблицы, но смысл? если уж связывать, то TITEM, TPROC_LIST и TOPERATIONS для расчета COST по TOPERATIONS.COST, чтобы не было нужды в
> С таким подходом нужно будет триггеры делать на обновление
> поля cost в tproc_list

← →
turbouser___ (2009-07-28 14:34) [20]

> _drug_

А так?
select i.ITEM_ID item_id , sum(case WHEN pt.id=1 then pc.amount*o.cost ELSE 0 end) AS cost1, sum(case WHEN pt.id=2 then pc.amount*o.cost ELSE 0 end) AS cost2, sum(case WHEN pt.id=3 then pc.amount*o.cost ELSE 0 end) AS cost3, sum(case WHEN pt.id=4 then pc.amount*o.cost ELSE 0 end) AS cost4 from titem i , toperations o , tproc_list pl , tproc_type pt , tproc_content pc where pl.item_id = i.item_id --связываем список процедур со списком предметов and pl.procedure_id = pc.proc_id --связываем список процедур с их содержанием and o.id = pc.operation_id --связываем содержание процедур с операциями and pt.id = pc.proc_type --связываем тип процедуры с содержанием (здесь ошибка в структуре) and pc.proc_type = 1 --выбираем жестко прошитый тип процедуры group by i.item_id; группируем, чтобы подсчитать сумму

← →
_drug_ (2009-07-28 15:30) [21]

> если уж связывать, то TITEM, TPROC_LIST и TOPERATIONS для
> расчета COST по TOPERATIONS.COST

Совершенно верно.

> turbouser___

если только убрать привязку к proc_type:
...and pc.proc_type = 1...
работает! и ХП не надо и ресурсов потребляет намного меньше, еще индекс настрою и вообще будет хрошо. век живи, век учись. а ведь просто все ))) Благодарю!

применение full join Найти похожие ветки