SQL вычесть множества

select id from a where not(id in (select id from b))

не ну это совсем фигня - тут же вложенный запрос

select id from a minus select id from b;

это для oracle в postgres есть EXCEPT оно работает еще медленней LEFT JOIN

предложенный анонимусом вариант работает бытрее left join'а

по крайней мере у меня на 8.3.1 и на тестовых данных :)

EXPLAIN ANALYZE select id from aaa left join bbb using(id) where bbb.id is null;

 Merge Left Join  (cost=0.00..35944.12 rows=500055 width=4) (actual time=0.072..1807.307 rows=857142 loops=1)
   Merge Cond: (aaa.id = bbb.id)
   Filter: (bbb.id IS NULL)
   ->  Index Scan using aaa_pkey on aaa  (cost=0.00..27694.99 rows=1000110 width=4) (actual time=0.031..652.195 rows=1000000 loops=1)
   ->  Index Scan using bbb_pkey on bbb  (cost=0.00..3963.13 rows=142858 width=4) (actual time=0.023..95.696 rows=142858 loops=1)
 Total runtime: 2026.807 ms

EXPLAIN ANALYZE select id from aaa where id not in (select id from bbb);

 Seq Scan on aaa  (cost=2346.72..18770.10 rows=500055 width=4) (actual time=194.817..1125.155 rows=857142 loops=1)
   Filter: (NOT (hashed subplan))
   SubPlan
     ->  Seq Scan on bbb  (cost=0.00..1989.58 rows=142858 width=4) (actual time=0.025..53.160 rows=142858 loops=1)
 Total runtime: 1335.373 ms

на моих тестах это совершенно не так :(

да еще забыл сказать - во второй таблице не может быть данных не содержащихся в первой таблице. те то-же что в первой или чего то нету. пытылся играться с FOREIGN KEY все еще в два раза тормознее.

create table d(id int not null, PRIMARY KEY(id), FOREIGN KEY(id) REFERENCES a(id)); create table e(id int not null, FOREIGN KEY(id) REFERENCES a(id));

у меня 10 миллионов строк - вложенный запрос напроч сливает (я устал ждать завершения)

по идее с FOREIGN KEY должно было бы быть быстрее... может прогнуть еще что-то нужно в настройках оптимизатора ?

Можно попробовать not exists вместо not in.

вполне очевидно, что инструкция foo not in (select bar...) будет быстрее, чем join'ы. Попробуйте мысленно проговорить операцию поиска вхождения и операцию склейки, станет понятно почему так.

это то же самое, если воспользоваться substr'ом вместо like '..%' - разница гигантская и сабстр в разы быстрее. Разумеется этот вариант подходит только для работы с фиксированной длиной. Еже ли требуется переменная длина, то акромя лайка никак.