Все высказывания о неоптимизируемости IN и EXISTS беспочвенны. Вот как раз IN выполнится быстрее всех остальных вариантов.

Я проводил специальные тесты для прояснения ситуации.

Дано: Две таблицы mdoc (заголовки документов, 100 000 строк) и mdocm (строки документов, 1 000 000 строк). В обеих таблицах по 2 индекса по номеру документа и DELETED().
Надо: Выбрать все номера документов из mdocm, которые присутствуют в mdoc и, наоборот, которые не присутствуют в mdoc (потерянные строки).

Выполнялось по три запроса для каждого случая: с использованием INNER JOIN и LEFT JOIN, с помощью IN, и с использованием EXISTS.
Для контроля за использованием файлов и индексов использовалась программа FileMon, позволяющая отследить запросы к файлам.


1 случай:
Запрос с использованием INNER JOIN выполнялся медленне всех:
select mdocm.numdoc from mdocm inner join mdoc ON mdocm.numdoc=mdoc.numdoc into cursor cur readwrite

При этом запросе происходит чтение ОБЕИХ таблиц и использование индекса NUMDOC файла mdocm для оптимизации объединения. Т.е. читается numdoc из mdoc, ищется в mdocm по индексу numdoc и затем читается mdocm для выборки значения numdoc.

Совершенно все по другому при использовании запросов с IN и EXISTS:
select numdoc from mdocm where numdoc in (select numdoc from mdoc)
select numdoc from mdocm where exists (select numdoc from mdoc where mdoc.numdoc=mdocm.numdoc)

Оба запроса выполняются по одинаковой схеме и, соотвественно, за одинаковое время (на порядок быстрее INNER JOIN).
Схема работы: идет чтение таблицы mdocm и использование индекса numdoc таблицы MDOC!Чтение таблицы mdoc вообще не происходит.

2 случай:
Вот здесь все гораздо тяжелее.
Все запросы выполняются практически за одинаковое время, хотя запрос с IN всегда выполнялся на 5-10% быстрее.
Во всех случаях происходит чтение обеих таблиц , что довольно долго, и использование индекса numdoc таблицы mdocm.

select mdocm.numdoc from mdocm left join mdoc ON mdocm.numdoc=mdoc.numdoc having mdoc.numdoc is null

select numdoc from mdocm where numdoc NOT in (select numdoc from mdoc)

select numdoc from mdocm where NOT exists (select numdoc from mdoc where mdoc.numdoc=mdocm.numdoc)

Выводы делайте сами. Да и посудите, зачем использовать JOIN таблицы, если вы не собираетесь ничего из нее выбирать? Естественно, IN с подзапросом гораздо предпочтительнее.
Все тесты проводились на VFP8 под WinXP.

Учтем на будущее
спасибо все кто выступал по данной схеме. Чесно говоря вы мне очень помогли не только разобратся с проблемой но и просветили на счет выборки.
Всем спасибо!

УПС ОШИБОЧКА!Вместо схемы читать темы

Кстати проверял на скорость работу по JOIN и EXISTS (в первой таблице 22000 записей во второй около 300 поля проиндексированы) заметных расхождений в скорости не наблюдал.

Что-то Вы загнули с первым случаем. Результат проверки на VFP6SP5+Win95 (rel 4.00.950) в секундах, при той же схеме:

<pre>
JOIN 6
IN 37
EXISTS 37
NOT JOIN 104
NOT IN 52
NOT EXISTS 52
</pre>

Т.е. если необходимо получить в выборке существующие записи,
то лучше использовать JOIN, а если НЕ существующие,
то лучше NOT IN или NOT EXISTS

Да, при многократном повторе одного и того же запроса
разборс результатов достигает 5...6%

Зато я проверил, разница в 10 раз (6 сек против 0.7 сек)
Причем именно, как написал WiRuc - если из второй таблицы ничего не нужно выбирать, а только использовать для отбора, похоже, лучше использовать IN.
SYS(3054) при этом почему-то показывает только использование индекса DELETED(), а использование индекса numdoc не показывает

И на закуску.

Вот такой запрос

select mdocm.numdoc ;
from mdocm ;
left join mdoc ;
ON mdocm.numdoc=mdoc.numdoc having COUNT(mdoc.numdoc)=0 ;
into cursor ctest nofilter

Выполняется уже за время сопоставимое с NOT IN и NOT EXISTS. У меня получилось около 56 секунд (в сравнении с 52 для NOT IN и NOT EXISTS). С учетом погрешности в 5...6% можно говорить о том, что скорость выполнения в этом случае практически одинаковая.

Т.е. самая медленная операция это сравнение с NULL, которая все и тормозит. Замена ее на сравнение с конкретным значением дает резкое увеличение производительности.

Странно...
А таблички большие? Что-то слишком быстро выполняются запросы.
Или в Vfp8 оптимизатор поменялся?
Кстати, последний запрос не совсем верен с точки зрения "правильного" SQL. На VFP8 он вообще не запускается, если не установить SET ENGINEBEHAVIOUR 70.
Для достоверности и понимания сути происходящего надо бы воспользоваться FileMon и посмотреть, что он там читает.

А если NVL-ом пользоваться?
WBR, Igor

<b>WiRuc</b>
Если по поводу размера - это вопрос ко мне, то я создал таблицы из одного поля типа Char(10), соответсвенно размеры mdoc=1МБ, mdocm=10МБ индексы чуть меньше (процентов на 20%), но примерно того же размера.

Около минуты - это слишком быстро?

А вот то, что стандартный запрос по INNER JOIN выполнялся у тебя так медленно (медленне всех), это вообще очень странно. Это же явно оптимизируемое выражение как ни крути. Попробуй выполнить все то же самое с настройкой SET ENGINEBEHAVIOUR 70 (я имею в виду только запросы с JOIN)

Кстати, в Win95 я что-то не нашел FileMon. Т.е. есть системный монитор, но это несколько не то.

<b>Igor Korolyov</b>
Пробовал я NVL() - получаю сообщение о синтаксической ошибке при попытке использования с HAVING любым способом