Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
То есть, простейшее решение выглядит так:
select A.* from A inner join B on B.a = A.a
union
select A.* from A inner join C on C.a = A.a

Но мне надо, чтобы селект был только один, причем таблицы в запрос могут добавляться только через join'ы.
Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?

Здравствуйте, Donz, Вы писали:

D>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>То есть, простейшее решение выглядит так:
D>select A.* from A inner join B on B.a = A.a
D>union
D>select A.* from A inner join C on C.a = A.a

D>Но мне надо, чтобы селект был только один, причем таблицы в запрос могут добавляться только через join'ы.
D>Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?

B и C? Или B ИЛИ C?

Здравствуйте, Donz, Вы писали:

D>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>То есть, простейшее решение выглядит так:
D>select A.* from A inner join B on B.a = A.a
D>union
D>select A.* from A inner join C on C.a = A.a

select A.* from A. left join B on B.a = A.a left join C on C.a = A.a
where notNull(B.a) and notNull(C.a)

не помню как на null проверять

Здравствуйте, cvetkov, Вы писали:

C>Здравствуйте, Donz, Вы писали:

D>>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>>То есть, простейшее решение выглядит так:
D>>select A.* from A inner join B on B.a = A.a
D>>union
D>>select A.* from A inner join C on C.a = A.a

C>select A.* from A. left join B on B.a = A.a left join C on C.a = A.a
C>where notNull(B.a) and notNull(C.a)

C>не помню как на null проверять

В данном случае проще написать
select A.* from A. join B on B.a = A.a join C on C.a = A.a
Результат будет тот же. Это работает для случая B и C, причем полным эквивалентом union будет не всегда — если в таблицах B или С есть несколько ссылок на A, то будет замножение результата. Нужен distinct или group by.

Для случая B ИЛИ C нужно писать
select distinct(A.a) from A left join B on B.a = A.a left join C on C.a = A.a
where B.a is not null or C.a is not null

Здравствуйте, Donz, Вы писали:

D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
Не знаю, правильно ли, но я последний раз делал так — (сразу на HQL, специально открыл исходники, на перфоманс вроде жалоб нет)

select A.* from A where (A.id in (select A1.id from A1 join A1.b B) or A.id in (select A2.id from A2 join A2.c C) ).

У меня joinов было 6, таким образом заработало гораздо шустрее, чем в случае с left join (тогда жалобы были на перфоманс ).
Когда-то кажется делал другим способом, и кажется без in, но напрочь забыл, и это осталось в другом проекте:
что-то вроде

select A.* from A, A join A.b B, A join A.c C where A.id=B.id or A.id=C.id

Увы, запросы я много писал более 4-х лет назад, сейчас один запрос в полгода максимум.

Здравствуйте, Donz, Вы писали:

D>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>То есть, простейшее решение выглядит так:
D>select A.* from A inner join B on B.a = A.a
D>union
D>select A.* from A inner join C on C.a = A.a

D>Но мне надо, чтобы селект был только один, причем таблицы в запрос могут добавляться только через join'ы.
D>Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?

А почему нельзя так:
select A.* from A inner join B on B.a = A.a
inner join C on C.a=A.a
?
и почему Вы берете "все" только из А?

Здравствуйте, svanir, Вы писали:

S>А почему нельзя так:
S>select A.* from A inner join B on B.a = A.a
S> inner join C on C.a=A.a
S>?
S>и почему Вы берете "все" только из А?
Здесь тогда будет не объединение, а пересечение. И условие — нужно показать все A, которые связаны либо с B, либо с C. Достаточно типичная задача кстати.

Здравствуйте, Donz, Вы писали:

D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?

SELECT A.*
FROM A
LEFT JOIN B ON B.a = A.a
LEFT JOIN C ON C.a = A.a
WHERE (B.a IS NOT NULL) OR (C.a IS NOT NULL)

Здравствуйте, Lloyd, Вы писали:

L>

L>SELECT A.*
L>FROM A
L>LEFT JOIN B ON B.a = A.a
L>LEFT JOIN C ON C.a = A.a
L>WHERE (B.a IS NOT NULL) OR (C.a IS NOT NULL)
L>

По крайней мере на MS SQL 2005 тормоза наблюдал страшные (20 секунд запрос выполнялся), сойдет только на небольших объемах. Сервер похоже сначала таблицы соединит (а они черти какого объема), а только потом фильтрует — жуть.

Здравствуйте, Donz, Вы писали:

D>Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?

А если создать вьюху с union-ами и использовать ее?

Здравствуйте, Romanzek, Вы писали:

D>>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>>То есть, простейшее решение выглядит так:
D>>select A.* from A inner join B on B.a = A.a
D>>union
D>>select A.* from A inner join C on C.a = A.a

R>B и C? Или B ИЛИ C?

Или B, или C. В общем надо выбрать один раз все записи, ссылки на которые есть хотя бы в одной из таблиц B или C. Нужен аналог приведенного запроса

Здравствуйте, elmal, Вы писали:

D>>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
E>Не знаю, правильно ли, но я последний раз делал так — (сразу на HQL, специально открыл исходники, на перфоманс вроде жалоб нет)
E>

E>select A.* from A where (A.id in (select A1.id from A1 join A1.b B) or A.id in (select A2.id from A2 join A2.c C) ).
E>

E>У меня joinов было 6, таким образом заработало гораздо шустрее, чем в случае с left join (тогда жалобы были на перфоманс ).

Запрос с двумя внутренними селектами и условиями in работает быстрее left join'ов? Что-то слабо верится...
Но в любом случае подселекты не катят — мне надо все сделать через Criteria API.

Здравствуйте, elmal, Вы писали:

L>>

L>>SELECT A.*
L>>FROM A
L>>LEFT JOIN B ON B.a = A.a
L>>LEFT JOIN C ON C.a = A.a
L>>WHERE (B.a IS NOT NULL) OR (C.a IS NOT NULL)
L>>

E>По крайней мере на MS SQL 2005 тормоза наблюдал страшные (20 секунд запрос выполнялся), сойдет только на небольших объемах. Сервер похоже сначала таблицы соединит (а они черти какого объема), а только потом фильтрует — жуть.

Можно подробнее? Оптимизацию запросов в БД начал изучать заново с полгода назад. Пока я в этом запросе ничего криминального не вижу.

Здравствуйте, Lloyd, Вы писали:

D>>Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
D>>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?

L>А если создать вьюху с union-ами и использовать ее?

Не подходит, так как выбранные объекты могут быть изменены. С вьхой придется делать еще один мэппинг уже конкретно на таблицу и перед изменениями таскать выбранные объекты еще одним запросом с условием in. В общем, не очень красиво и захламляет код.
Плюс это повлечет изменение БД, что в моем случае несколько геморройно.

Здравствуйте, svanir, Вы писали:

D>>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>>То есть, простейшее решение выглядит так:
D>>select A.* from A inner join B on B.a = A.a
D>>union
D>>select A.* from A inner join C on C.a = A.a

S>А почему нельзя так:
S>select A.* from A inner join B on B.a = A.a
S> inner join C on C.a=A.a
S>?

Этот запрос выберет записи, которые одновременно находятся и в B, и в C. Мне же надо условие "или в B, или в C". elmal уже написал, в общем.

S>и почему Вы берете "все" только из А?
Не совсем понял вопрос. Надо мне так, взять только все данные из таблицы A, а остальные таблицы нужны только для ограничения выборки.

Здравствуйте, Donz, Вы писали:

D>Можно подробнее? Оптимизацию запросов в БД начал изучать заново с полгода назад. Пока я в этом запросе ничего криминального не вижу.
Да не могу подробнее, тут все от оптимизатора зависит. Я ж говорю, что пишу один более-менее сложный запрос в полгода, а оптимизация ... на одном сервере будет шустро работать так, на другом иначе — не угадаешь, а все тонкости учить для каждого сервера — не окупится, слишком часто они меняются, по крайней мере у меня. Просто вот такой запрос в случае с left join у меня узким местом оказался. Когда данных было мало, все шустро, наполнили базу — тормоза сразу страшные (а под нагрузочным тестированием вообще жуть). Переписал в итоге на вариант с in — практически моментально стало (точнее время выполнения запроса меньше времени перерисовки, соответственно дальше я отпимизировать не стал). И еще были проблемы с left join на MS SQL (его генерил hibernate), тоже одна сущность грузилась в результате несколько секунд — переписал с использованием вложенных подзапросов, сразу все шустро стало. LEFT JOIN я бы потому старался избегать, часто сервер делает сначала его, а только потом фильтрацию (мои догадки это, только этим могу тормоза объяснить).
Ну и я там еще один вариант показывал (пересечение таблицы сама с собой который), по идее он самый шустрый должен быть (я не уверен что там я буз ошибок написал), на firebird я когда пересечения вот такие делал страшные для таблиц в миллионы записей — вполне шустро работало. А in мне не очень нравится, интуиция просто подсказывает что in лучше избегать, оптимизатор может не очень хорошо построить план выполнения.

Здравствуйте, Donz, Вы писали:

D>Запрос с двумя внутренними селектами и условиями in работает быстрее left join'ов? Что-то слабо верится...
У меня тогда этих left join было штук 6, и каждый еще соединялся еще с несколькоми таблицами (ох хотелось тогда высказать все, что я думаю о структуре базы, еле сдержался ). С in заработало быстрее, причем раз в 100 — скорость замерял.
D>Но в любом случае подселекты не катят — мне надо все сделать через Criteria API.
Нижний вариант пробовал? Он явно должен быть самым шустрым, так как там только join по первичному ключу идет (я правда не знаю, можно ли через Criteria API пересечения таблиц как я указал делать). Возможно я ошибся, и я что-то забыл в запросе, надо пробовать, но один раз у меня точно похожим образом union на хибернейте сделать получилось.

Здравствуйте, elmal, Вы писали:

E>У меня тогда этих left join было штук 6, и каждый еще соединялся еще с несколькоми таблицами (ох хотелось тогда высказать все, что я думаю о структуре базы, еле сдержался ). С in заработало быстрее, причем раз в 100 — скорость замерял.
Хотя, справедливости ради, запрос у меня был несколько посложнее, там у меня была задача похожая на твою, но там связи были по простым аттрибутам, которые с таблицей B были связана еще через 5 таблиц . Они не были даже внешними ключами и на них даже индексов не висело, потому и такой результат. Потом индексы добавили, я попросил того, кто занимается оптимизацией предыдущий запрос проверить, вроде один черт left join медленнее чем вложенные селекты с in оказалось.

Здравствуйте, elmal, Вы писали:

D>>Запрос с двумя внутренними селектами и условиями in работает быстрее left join'ов? Что-то слабо верится...
E>У меня тогда этих left join было штук 6, и каждый еще соединялся еще с несколькоми таблицами (ох хотелось тогда высказать все, что я думаю о структуре базы, еле сдержался ). С in заработало быстрее, причем раз в 100 — скорость замерял.
D>>Но в любом случае подселекты не катят — мне надо все сделать через Criteria API.
E>Нижний вариант пробовал? Он явно должен быть самым шустрым, так как там только join по первичному ключу идет (я правда не знаю, можно ли через Criteria API пересечения таблиц как я указал делать). Возможно я ошибся, и я что-то забыл в запросе, надо пробовать, но один раз у меня точно похожим образом union на хибернейте сделать получилось.

Нижний — это left join и проверка на null? Пока нет, пробовать буду в понедельник.
За информацию спасибо, буду проверять, так как запрос будет очень часто исполняемым.

Здравствуйте, Donz, Вы писали:

D>Нижний — это left join и проверка на null? Пока нет, пробовать буду в понедельник.
Я вариант с left join вообще не приводил. Он работать то будет, тут я не сомневаюсь, вопрос в скорости. Второй вариант, который я предложил — пересечение таблицы и двумя джоинами, как любят ораклисты писать (не джоин, а select from A,B,C where ... ) вот он очень шустро должен выполняться теоретически, возможно даже шустрее, чем в случае с использованием union (неисповедимы пути оптимизатора запросов).
Еще раз —

select A.* from A, A join A.b B, A join A.c C where A.id=B.id or A.id=C.id

Вот только не факт, что я его правильно написал, но один раз я точно что-то подобное проворачивал на хибернейте, помню точно, что я делал union и без left join, и без in, и основная идея была именно такая — пересечение таблицы с собой под разными алиасами (задача была другой помнится). Просьба попробовать, и сообщить, если получится — я тогда в следующий раз, когда это понадобится, поиском найду, а то опять забуду . На деле, синтаксис SQL весьма избыточен, и при желании одним запросом можно сделать многое, даже если куча фичь, вроде union, вложенных подзапросов и т.д не поддерживается.