C++ Builder
| Главная | Уроки | Статьи | FAQ | Форум | Downloads | Литература | Ссылки | RXLib | Диски |

 
Все значения кроме тех, что есть в другой таблице, все значения кроме тех, что есть в друго
Kop123
Отправлено: 12.01.2005, 14:30


Не зарегистрирован







Как выбрать из таблицы все значения кроме тех, что есть в другой таблице?
Bond
Отправлено: 12.01.2005, 15:16


Станционный диспетчер

Группа: Участник
Сообщений: 142



Использовал IBExpert

table1 (field1 smallint); {1,2,3,4,5,6,7}
table2 (field1 smallint); {3,6}
поля field1 ключевые

Запрос:
CODE
select "table1"."field1" from "table1"
 where "table1"."field1" not in
 (select "table2"."field1" from "table2");

Результат:
CODE
1
2
4
5
7
xim
Отправлено: 12.01.2005, 15:42


Станционный диспетчер

Группа: Участник
Сообщений: 143



В Transact SQL:
CODE

select * from table1 t1 where
not exists(select <common_field> from table2 t2 where
t2.<common_field>=t1.<common_field>)
AVC
Отправлено: 12.01.2005, 16:02


Ветеран

Группа: Модератор
Сообщений: 1583



Хотя вариант Bond синтаксически более красивый, сталкивался с ситуациями, когда он выполнялся неоправдано долго и даже приводил к краху.
Поэтому я бы написал так:
Select t1.* --, t2.*
From t1 left outer join t2 on (t2.ID = t1.ID)
Where t2.ID is Null

PS. Для более продуктивного совета желательно уточнять СУБД.

Отредактировано AVC — 12/01/2005, 16:06
Bond
Отправлено: 12.01.2005, 17:10


Станционный диспетчер

Группа: Участник
Сообщений: 142



smile.gif)
Согласен с AVC — именно для этого и есть данный оператор smile.gif
CODE
select "table1"."field1" from "table1"
   left outer join "table2" on ("table2"."field1" = "table1"."field1")
       where "table2"."field1" is null

Производительность запроса значительно возросла.
Я пользовался только INNER JOIN smile.gif — спасибо smile.gif)
xim
Отправлено: 13.01.2005, 11:56


Станционный диспетчер

Группа: Участник
Сообщений: 143



Под SQL Server-ом инструкции:

CODE

select * from table1 t1
where t1.<common_field> not in
(select t2.<common_field> from table2 t2)

&&

(

select * from table1 t1 where
not exists(select <common_field> from table2 t2 where
t2.<common_field>=t1.<common_field>)

||

select * from table1 t1 where
not exists(select null from table2 t2 where
t2.<common_field>=t1.<common_field>)

)


имеют одинаковую стоимость запроса (судя по плану) и выполняются быстрее чем инструкция, приведенная AVC, т.к. в
CODE
Where t2.ID is Null
тратится дополнительное время на фильтрацию записей не найденных во 2 табл.

2AVC:
Что касается "краховых" ситуаций — хотелось бы посмотреть на кусок скрипта и настройки контекста (сессии)
AVC
Отправлено: 13.01.2005, 12:41


Ветеран

Группа: Модератор
Сообщений: 1583



2xim
Я же не утверждаю, что это панацея, а просто уточнил что сталкивался с такими ситуациями в реальной жизни.
Многое зависит от используемого сервера и его настроек.

Пример
Oracle 8i, статистики не собраны
CODE

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

-- Работет ЧЕРЕЗВЫЧАЙНО медленно и забивает pool (у клиента) вызывая крах
Select Count(*) From HHC.Address ADR
Where ADR.AddressID not in (Select PA.AddressID From HHC.PersonalAccount PA);

13.01.2005 11:10
Дождаться ответа терпения не хватило — запрос снят через 5 мин
План оптимизации — стоимость 14
SELECT STATEMENT Optimizer Mode=CHOOSE        
 SORT AGGREGATE                
   FILTER  
     INDEX FAST FULL SCAN SYS_C001012                        
     TABLE ACCESS FULL PERSONALACCOUNT


CODE

-- Около 1 сек при объёмах: PA 41695, ADR 41768
Select Count(*)
From (
Select ADR.AddressID, PA.PersonalAccountID
From HHC.Address ADR, HHC.PersonalAccount PA
Where PA.AddressID (+) = ADR.AddressID
) tmp
Where tmp.PersonalAccountID is Null

13.01.2005 11:30
Время выполнения 359 mc
План оптимизации — стоимость 320
SELECT STATEMENT Optimizer Mode=CHOOSE
 SORT AGGREGATE
   FILTER
     HASH JOIN OUTER
       INDEX FAST FULL SCAN SYS_C001012
       TABLE ACCESS FULL PERSONALACCOUNT


Комментарии излишни?
Сбор статистик может исправить ситуацию.

Все зависит от конкретной ситуации. smile.gif
AVC
Отправлено: 13.01.2005, 13:47


Ветеран

Группа: Модератор
Сообщений: 1583



2xim
Дождался таки выполнения первого из запросов 34 мин 25 сек (сравните с 320 мс smile.gif )

Фрагменты файла трассировки
Запрос с not in
CODE

call query rows
Fetch 38263124 1


Запрос с Null
CODE

call query rows
Fetch 1432 1


Отредактировано AVC — 13/01/2005, 13:51
olegenty
Отправлено: 13.01.2005, 14:27


Ветеран

Группа: Модератор
Сообщений: 2412



CODE

select
   *
from
   t1
where
   not exists (select t2.id from t2 where t2.id = t1.id)

т.е. вариант xim — самое то.
недавно на SQL.RU это обсуждалось. вроде как ничего более эффективного не придумали...

по внутренней выборке всегда выбирается не более 1-й записи, что рульнее как чем IN — все записи, так и чем LEFT OUTER JOIN — тоже все.
при наличие индексов по полю, для данного примера id. иначе будут варианты и всё будет зависеть от размеров таблиц.
AVC
Отправлено: 13.01.2005, 15:00


Ветеран

Группа: Модератор
Сообщений: 1583



Oracle 8i
Фрагмент файла трассировки
CODE

-- 1 s
Select Count(*) From HHC.Address adr
Where not exists (Select pa.PersonalAccountID From HHC.PersonalAccount pa Where pa.AddressID = adr.AddressID)

call cpu query rows
Fetch 1.12 115058 1


Трассировка запросов показывает, что при условии Oracle8i с теми установками которые стоят у меня вариант с Null самый выгодный
CODE

тип cpu зачитано блоков
Null 0.28 1432
Exists 1.12 115058
not in 2075.67 38263124


Отредактировано AVC — 13/01/2005, 15:27
olegenty
Отправлено: 13.01.2005, 15:41


Ветеран

Группа: Модератор
Сообщений: 2412



проверил. для MSSQL это неверно. хотя, возможно тоже есть варианты с опциями...

Вернуться в Работа с базами данных в C++Builder