Explaination of queries for SO: question 26671671

d-side@DSN-X:~/git/workers$ rails c
Loading development environment (Rails 4.2.0.beta2)
2.1.3 :001 > @event = Event.first
  Event Load (0.4ms)  SELECT  `events`.* FROM `events`  ORDER BY `events`.`id` ASC LIMIT 1
 => #<Event id: 1, created_at: "2014-10-31 11:10:45", updated_at: "2014-10-31 11:10:45">
2.1.3 :002 > Worker.where(WorkerMembership.where("workers.id = worker_memberships.worker_id").where("worker_memberships.event_id = ?", @event.id).exists.not).explain
  Worker Load (0.3ms)  SELECT `workers`.* FROM `workers` WHERE (NOT (EXISTS (SELECT `worker_memberships`.* FROM `worker_memberships` WHERE (workers.id = worker_memberships.worker_id) AND (worker_memberships.event_id = 1))))
 => EXPLAIN for: SELECT `workers`.* FROM `workers` WHERE (NOT (EXISTS (SELECT `worker_memberships`.* FROM `worker_memberships` WHERE (workers.id = worker_memberships.worker_id) AND (worker_memberships.event_id = 1))))
+----+--------------------+--------------------+------+----------------------------------------------------------------------------+---------------------------------------+---------+--------------------+------+-------------+
| id | select_type        | table              | type | possible_keys                                                              | key                                   | key_len | ref                | rows | Extra       |
+----+--------------------+--------------------+------+----------------------------------------------------------------------------+---------------------------------------+---------+--------------------+------+-------------+
|  1 | PRIMARY            | workers            | ALL  | NULL                                                                       | NULL                                  | NULL    | NULL               |    2 | Using where |
|  2 | DEPENDENT SUBQUERY | worker_memberships | ref  | index_worker_memberships_on_worker_id,index_worker_memberships_on_event_id | index_worker_memberships_on_worker_id | 5       | workers.workers.id |    1 | Using where |
+----+--------------------+--------------------+------+----------------------------------------------------------------------------+---------------------------------------+---------+--------------------+------+-------------+
2 rows in set (0.00 sec)

2.1.3 :003 > Worker.where.not(id: WorkerMembership.select(:worker_id).where(event: @event) ).explain
  Worker Load (0.3ms)  SELECT `workers`.* FROM `workers` WHERE (`workers`.`id` NOT IN (SELECT `worker_memberships`.`worker_id` FROM `worker_memberships` WHERE `worker_memberships`.`event_id` = 1))
 => EXPLAIN for: SELECT `workers`.* FROM `workers` WHERE (`workers`.`id` NOT IN (SELECT `worker_memberships`.`worker_id` FROM `worker_memberships` WHERE `worker_memberships`.`event_id` = 1))
+----+--------------------+--------------------+------+----------------------------------------------------------------------------+--------------------------------------+---------+-------+------+-------------+
| id | select_type        | table              | type | possible_keys                                                              | key                                  | key_len | ref   | rows | Extra       |
+----+--------------------+--------------------+------+----------------------------------------------------------------------------+--------------------------------------+---------+-------+------+-------------+
|  1 | PRIMARY            | workers            | ALL  | NULL                                                                       | NULL                                 | NULL    | NULL  |    2 | Using where |
|  2 | DEPENDENT SUBQUERY | worker_memberships | ref  | index_worker_memberships_on_worker_id,index_worker_memberships_on_event_id | index_worker_memberships_on_event_id | 5       | const |    1 | Using where |
+----+--------------------+--------------------+------+----------------------------------------------------------------------------+--------------------------------------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

2.1.3 :004 > Worker.where(WorkerMembership.where("workers.id = worker_memberships.worker_id").where("worker_memberships.event_id = ?", @event.id).exists.not)
  Worker Load (0.4ms)  SELECT `workers`.* FROM `workers` WHERE (NOT (EXISTS (SELECT `worker_memberships`.* FROM `worker_memberships` WHERE (workers.id = worker_memberships.worker_id) AND (worker_memberships.event_id = 1))))
 => #<ActiveRecord::Relation [#<Worker id: 2, created_at: "2014-10-31 11:29:31", updated_at: "2014-10-31 11:29:31">]>
2.1.3 :005 > Worker.where.not(id: WorkerMembership.select(:worker_id).where(event: @event) )
  Worker Load (0.4ms)  SELECT `workers`.* FROM `workers` WHERE (`workers`.`id` NOT IN (SELECT `worker_memberships`.`worker_id` FROM `worker_memberships` WHERE `worker_memberships`.`event_id` = 1))
 => #<ActiveRecord::Relation [#<Worker id: 2, created_at: "2014-10-31 11:29:31", updated_at: "2014-10-31 11:29:31">]>
2.1.3 :006 >