Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / FoxPro, Visual FoxPro Новый топик    Ответить
 Предикат NOT IN  [new]
boyza
Member

Откуда:
Сообщений: 11
Здравствуйте
У меня такая задача. Нужно вывести записи из таблицы matlist , id которых нет в таблице demands (matlist - справочник)

SELECT DISTINCT matlist.id_mat, matlist.nomenclature, matlist.style_mat FROM matlist JOIN demands ON matlist.id_mat IN (demands.id_mat)
---
Так правильно показывает, какие материалы встречаются в demands
Стоит добавить NOT - показывает абсолютно все материалы из справочника (а не инвертируется). NOT добавлял как и после ON, так и перед IN ...

Делал еще так.
SELECT id_mat INTO ARRAY lalala FROM demands

SELECT DISTINCT matlist.id_mat, matlist.nomenclature, matlist.style_mat FROM matlist JOIN demands ON matlist.id_mat IN (lalala)
---
Здесь как и с НОТ, так и без него рассматривается только первая запись в массиве

Что я делаю не так?
Спасибо за ответ
p.s. foxpro 6
4 май 05, 15:42    [1516334]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
ВладимирМ
Member

Откуда: г. Москва
Сообщений: 7864
SELECT DISTINCT matlist.id_mat, matlist.nomenclature, matlist.style_mat ;
FROM matlist ;
WHERE matlist.id_mat NOT IN (SELECT id_mat FROM demands) 
4 май 05, 15:59    [1516395]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
leaf
Member [заблокирован]

Откуда: Ростов-на-Дону --> Москва
Сообщений: 2661
если не ошибаюсь
select matlist.id from matlist where id ! in (select id from demands)
4 май 05, 16:02    [1516407]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
boyza
Member

Откуда:
Сообщений: 11
спасибо! :)

(уже сам не знаю зачем я жоин туда вплел...)
4 май 05, 16:29    [1516533]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
ank_guest
Guest
ВладимирМ
SELECT DISTINCT matlist.id_mat, matlist.nomenclature, matlist.style_mat ;
FROM matlist ;
WHERE matlist.id_mat NOT IN (SELECT id_mat FROM demands) 

SELECT much faster with IS NULL
SELECT DISTINCT matlist.id_mat, matlist.nomenclature, matlist.style_mat ;
FROM matlist ;
LEFT JOIN demands ON matlist.id_mat =demands.id_mat 
WHERE demands.id_mat IS NULL
5 май 05, 15:35    [1519607]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
ВладимирМ
Member

Откуда: г. Москва
Сообщений: 7864
2 ank_guest

Нет. Не будет.

Тестировал на VFP6SP5 года 2 назад именно подобные запросы. Было 3 варианта:

NOT IN
LEFT JOIN ... HAVING IsNull()=.T.
NOT EXISTS(...)

Так вот, NOT IN из них самый быстрый. LEFT JOIN и NOT EXISTS() примерно одинаковые по времени, но раза в 2 медленнее, чем NOT IN

Покопайся в архиве на http://forum.foxclub.ru/list.php?f=5 Было обсуждение результатов этого тестирования. Сейчас не помню ссылку
5 май 05, 15:56    [1519738]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
ВладимирМ
Member

Откуда: г. Москва
Сообщений: 7864
Вот. Нашел тему с обсуждением сравнения скорости подобных запросов

http://forum.foxclub.ru/read.php?f=5&i=60927&t=60819&cp=1
5 май 05, 16:04    [1519792]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
PaulWist
Guest
ank_guest

Отличное решение, за исключением одного НО - LEFT JOIN тут не нужен, поскольку условие в WHERE делает обьединение INNER JOIN.

CLEAR
?SET('DELETED')
* табличка с наименованиями
CREATE CURSOR test (id i, cName c(10))
SELECT test
INDEX ON id TAG id
* табличка с их встречаемостью
CREATE CURSOR test1 (id i, ID_Test i)
SELECT test1
INDEX ON id_test TAG id_test

INSERT INTO test (id, cName) VALUES (1, 'one')
INSERT INTO test (id, cName) VALUES (2, 'two')
INSERT INTO test (id, cName) VALUES (3, 'three')

INSERT INTO test1 (id, ID_Test) VALUES (1, 1)
INSERT INTO test1 (id, ID_Test) VALUES (2, 1)
INSERT INTO test1 (id, ID_Test) VALUES (3, 3)

* получаем одну запись, хотя просили LEFT
SELECT t.* FROM test t LEFT JOIN test1 t1 on t.id = t1.id_test where ISNULL(t1.id_test) nofilter
* а вот истинный LEFT включающий все записи из левой таблицы (3 штуки)
SELECT t.* FROM test t LEFT JOIN test1 t1 on t.id = t1.id_test and ISNULL(t1.id_test) nofilter
5 май 05, 16:07    [1519814]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
leaf
Member [заблокирован]

Откуда: Ростов-на-Дону --> Москва
Сообщений: 2661
дико извеняюсь за вмешательство но у меня и в первом и во втором запросе по три записи чего не понял?
5 май 05, 16:16    [1519880]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
PaulWist
Guest
Опс.

Тест в 5-ке возвращает
для 1-ого - 0 записей
для 2-ого - 3 записи

Тест в 7-ке возвращает
для 1-ого - 3 записей
для 2-ого - 3 записи

Тест в 9-ке возвращает
для 1-ого - 1 записей
для 2-ого - 3 записи

Как видим прогрес в SQL движке налицо.
5 май 05, 16:29    [1519963]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
PaulWist
Guest
И ещё убрал индексы

5-ка
не отреагировала

7-ка
стала правильно работать

9-ка
не повлияло
5 май 05, 16:33    [1519980]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
Igor Korolyov
Member

Откуда: Гомель, Беларусь
Сообщений: 2512

Hi PaulWist!

На самом деле это был глюк в SQL движке - и его поправили - то-ли полностью в 7-ке, то-ли частично в 7-ке и окончательно в 8-ке :)
Проявлялся он как раз с IS NULL, ISNULL() и NVL()

Posted via ActualForum NNTP Server 1.1

6 май 05, 01:15    [1521038]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
PaulWist
Guest
Привет, Игорь

Igor Korolyov
и его поправили - то-ли полностью в 7-ке, то-ли частично в 7-ке и окончательно в 8-ке


Ну ты хоть до конца все мои посты прочел, а если прочел , то наверное смог сделать вывод, что поправили в 9-ке точно, а в 7-ке всё было по старому.
6 май 05, 09:57    [1521399]     Ответить | Цитировать Сообщить модератору
 Re: Предикат NOT IN  [new]
Igor Korolyov
Member

Откуда: Гомель, Беларусь
Сообщений: 2512

Hi PaulWist!

1) Прочёл, но не все буквы угадал :)
2) В 7-ке ЯВНО это правили - без индекса оно работает корректно, как ты сам и заметил. Но не до конце сделали... С индексом выходит ерунда :(
3) А вот в 8-ке видимо окончательно вылечили - там и с индексом и без него работает как положено.

Posted via ActualForum NNTP Server 1.1

8 май 05, 16:58    [1525752]     Ответить | Цитировать Сообщить модератору
Все форумы / FoxPro, Visual FoxPro Ответить