left join is not returning proper information

Question

I have a table courses and a table volunteer_rosters. courses contains a list of all courses and volunteer_rosters contains a list of volunteers teaching/assisting on the course.

I want to write a query that returns a list of all courses that do not have any volunteers assigned to it. This is what is happening:

courses     
-------     
id 
 1  
 3  
 4  
 5  
 6  

volunteer_courses
-----------------
id 1 course_id 1  
id 2 course_id 1  
id 3 course_id 1  
id 5 course_id 3  
id 6 course_id 3

All of the below queries are returning
course_id 3
course_id 4
course_id 5

Why is course_id 1 (correctly) being left out, but course_id 3 is not???

Course.joins("LEFT JOIN student_rosters ON courses.id = student_rosters.course_id where student_rosters.course_id is null")

Course.joins("LEFT JOIN student_rosters ON courses.id = student_rosters.course_id").where(student_rosters: {id: nil})

Course.includes(:student_rosters).references(:student_rosters).where(student_rosters: {id: nil})

Course.includes(:student_rosters).references(:student_rosters).where('student_rosters.id = ?', nil)

Course.joins("LEFT JOIN student_rosters ON courses.id = student_rosters.course_id").where(student_rosters: {id: nil})

Same exact question, but none of the solutions are working for me:

LEFT OUTER JOIN in Rails 4

Can you write a working query in regular sql, without all this activerecord stuff? — Strawberry, May 27 '18 at 07:16
This is the query that every query above generates: `SELECT "courses".* FROM "courses" LEFT JOIN student_rosters ON courses.id = student_rosters.course_id WHERE "student_rosters"."id" IS NULL ` — Yitzhak, May 27 '18 at 07:40

score 2 · Accepted Answer · answered May 27 '18 at 07:10

2

There's no need to use join. Try this:

select id from Courses
where id not in (select course_id from volunteer_courses)

answered May 27 '18 at 07:10

Michał Turczyn

32,028
14
47
69

1

Brilliant, I love nested selects. This should have come to me...however learning how to write good sql queries in active record is killing me slowly....for anyone who needs this query in active record I kind of just hacked it because I can't be bothered spending the time learning it. `Course.where('id not in (select course_id from volunteer_rosters)') ` – Yitzhak May 27 '18 at 07:46

score 0 · Answer 2 · answered May 27 '18 at 12:53

0

Although there is no need to use joins, using not in is dangerous with a subquery. It does not do what you really want. If any row in the subquery returns a NULL value, then the outer query returns nothing. No rows at all.

For this reason, I strongly recommend using not exists and avoiding not in with subqueries:

select c.id
from Courses c
where not exists (select 1 from volunteer_courses vc where vc.course_id = c.id);

Or the left join version:

select c.id
from Courses c left join
     volunteer_courses vc 
     on vc.course_id = c.id
where vc.course_id is null;

In addition, both of these can take advantage of an index on volunteer_courses(course_id).

answered May 27 '18 at 12:53

Gordon Linoff

1,242,037
58
646
786

I tried your first example `SELECT "courses".* FROM "courses" WHERE (not exists (select 1 from volunteer_rosters where role = 0))` and it did not work, it returned nothing while there indeed are rows that should have returned. – Yitzhak May 27 '18 at 13:09
@Yitzhak . . . The query in your comment and the version in my question are not at all the same. – Gordon Linoff May 27 '18 at 20:07

left join is not returning proper information

2 Answers2