IN, JOIN и EXISTS. Есть ли разница?!

Периодически встречаю такое поверье - логически эквивалентные запросы на одних и тех же данных будут физически по-разному выполняться движком СУБД. В зависимости от того, как записано условие - через in, join или exists.

Вот буквально в этом месяце на техническом собеседовании в Тинькофф собеседующий мне это утверждал.

У меня на ноуте установлен PostgreSQL, проверим для этой СУБД.

--у меня установлен PostgreSQL 12.6
select version()
--PostgreSQL 12.6, compiled by Visual C++ build 1914, 64-bit

Будут использоваться таблицы из демонстрационной базы https://postgrespro.ru/education/demodb

Задача 1 - вернуть информацию из таблицы ticket_flights для всех пассажиров ALEKSANDRA ARKHIPOVA. Всего в таблице bookings.tickets 124 таких записи.

1.1. Используем join и смотрим план запроса

explain (analyze, costs off, timing off)
select tf.*
from bookings.ticket_flights tf join bookings.tickets t 
  on t.ticket_no = tf.ticket_no
where t.passenger_name = 'ALEKSANDRA ARKHIPOVA'
/*Gather (actual rows=343 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop (actual rows=114 loops=3)
        ->  Parallel Seq Scan on tickets t (actual rows=41 loops=3)
              Filter: (passenger_name = 'ALEKSANDRA ARKHIPOVA'::text)
              Rows Removed by Filter: 983244
        ->  Index Scan using ticket_flights_pkey on ticket_flights tf (actual rows=3 loops=124)
              Index Cond: (ticket_no = t.ticket_no)
Planning Time: 10.497 ms
Execution Time: 390.187 ms*/

1.2. Используем in

explain (analyze, costs off, timing off)
select tf.*
from bookings.ticket_flights tf 
where  tf.ticket_no in (select t.ticket_no from bookings.tickets t where t.passenger_name = 'ALEKSANDRA ARKHIPOVA')
/*Gather (actual rows=343 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop (actual rows=114 loops=3)
        ->  Parallel Seq Scan on tickets t (actual rows=41 loops=3)
              Filter: (passenger_name = 'ALEKSANDRA ARKHIPOVA'::text)
              Rows Removed by Filter: 983244
        ->  Index Scan using ticket_flights_pkey on ticket_flights tf (actual rows=3 loops=124)
              Index Cond: (ticket_no = t.ticket_no)
Planning Time: 0.327 ms
Execution Time: 361.855 ms*/

1.3. Используем exists

explain (analyze, costs off, timing off)
select tf.*
from bookings.ticket_flights tf 
where exists 
  (select 1 from bookings.tickets t 
   where t.passenger_name = 'ALEKSANDRA ARKHIPOVA' 
   and t.ticket_no = tf.ticket_no)
/*Gather (actual rows=343 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop (actual rows=114 loops=3)
        ->  Parallel Seq Scan on tickets t (actual rows=41 loops=3)
              Filter: (passenger_name = 'ALEKSANDRA ARKHIPOVA'::text)
              Rows Removed by Filter: 983244
        ->  Index Scan using ticket_flights_pkey on ticket_flights tf (actual rows=3 loops=124)
              Index Cond: (ticket_no = t.ticket_no)
Planning Time: 0.337 ms
Execution Time: 335.240 ms*/

Видно, что во всех 3 случаях был применён один и тот же план, оптимизатор выбрал реализацию соединения через nested loop.

Задача 2 - вернуть информацию из таблицы ticket_flights для всех пассажиров c именами, начинающимися на заглавную A. Всего в таблице bookings.tickets 563814 таких записи (т.е. много).

2.1. Используем join

explain (analyze, costs off, timing off)
select tf.*
from bookings.ticket_flights tf join bookings.tickets t on t.ticket_no = tf.ticket_no
where t.passenger_name like 'A%'
/*Hash Join (actual rows=1604919 loops=1)
  Hash Cond: (tf.ticket_no = t.ticket_no)
  ->  Seq Scan on ticket_flights tf (actual rows=8391852 loops=1)
  ->  Hash (actual rows=563814 loops=1)
        Buckets: 131072  Batches: 16  Memory Usage: 2606kB
        ->  Seq Scan on tickets t (actual rows=563814 loops=1)
              Filter: (passenger_name ~~ 'A%'::text)
              Rows Removed by Filter: 2386043
Planning Time: 11.102 ms
Execution Time: 10048.527 ms*/

2.2. Используем in

explain (analyze, costs off, timing off)
select tf.*
from bookings.ticket_flights tf 
where  tf.ticket_no in (select t.ticket_no from bookings.tickets t where t.passenger_name like 'A%')
/*Hash Join (actual rows=1604919 loops=1)
  Hash Cond: (tf.ticket_no = t.ticket_no)
  ->  Seq Scan on ticket_flights tf (actual rows=8391852 loops=1)
  ->  Hash (actual rows=563814 loops=1)
        Buckets: 131072  Batches: 16  Memory Usage: 2606kB
        ->  Seq Scan on tickets t (actual rows=563814 loops=1)
              Filter: (passenger_name ~~ 'A%'::text)
              Rows Removed by Filter: 2386043
Planning Time: 0.312 ms
Execution Time: 8913.533 ms*/

2.3. Используем exists

explain (analyze, costs off, timing off)
select tf.*
from bookings.ticket_flights tf 
where exists (select 1 from bookings.tickets t where t.passenger_name like 'A%' and t.ticket_no = tf.ticket_no)
/*Hash Join (actual rows=1604919 loops=1)
  Hash Cond: (tf.ticket_no = t.ticket_no)
  ->  Seq Scan on ticket_flights tf (actual rows=8391852 loops=1)
  ->  Hash (actual rows=563814 loops=1)
        Buckets: 131072  Batches: 16  Memory Usage: 2606kB
        ->  Seq Scan on tickets t (actual rows=563814 loops=1)
              Filter: (passenger_name ~~ 'A%'::text)
              Rows Removed by Filter: 2386043
Planning Time: 0.341 ms
Execution Time: 8580.005 ms*/

Также во всех 3 случаях был применён один и тот же план, оптимизатор выбрал реализацию соединения hash join.

Итого, для одинаковых логически запросов были выполнены одинаковые планы, вне зависимости от того, как было оформлено условие на датасет - через in, join или exists. Для маленького датасета была выбрана реализация nested loop, для большого - hash join.

Возможно, для каких-то старых версий каких-то СУБД поверье из преамбулы является реальностью. Помню, сам на ms sql 2008 in на join переписывал, и вроде как помогало.

Пишите в комментариях, с какими случаями сталкивались.

Или что я не так сделал при написании заметки.