SQLite query select all records that does not exist in another table

Question

I am having some problem when trying to perform a SQLite query to get the records which does not exist from another table. Basically I have two database tables:

Database table

My exercise table stored all the exercises available whereas the bookedExercise table store the exercises booked by each users. What I am trying to do is, for example if the exercise does exist in the bookedExercise, it should be filtered out.

Here is the SQLite query which I used:

SELECT exercise.exerciseID, exercise.exerciseType, exercise.amout FROM exercise LEFT JOIN bookedExercise WHERE exercise.exerciseID = bookedExercise.exerciseID AND bookedExercise.exerciseID IS NULL

However, it returned me empty records. Any ideas?

Thanks in advance.

Brendan Russo · Accepted Answer · 2016-10-07T01:54:51.593

5

If you're fine with not using joins you could use

SELECT * FROM exercise WHERE exerciseID not in (SELECT exerciseID FROM bookedExercise)

edited Oct 07 '16 at 01:54

answered Jul 31 '14 at 01:54

Brendan Russo

176
3
11

1

+1 an equally acceptable solution. For some DMBS systems the query within the in clause is less efficient than a hash-join/anti-join for a very large number of values, but I'm not sure if this is true for SQLite or not. Unless you're in the 10,000+ range, it probably doesn't matter – Hambone Jul 31 '14 at 02:10
@boblikepie It works! But is there any way to enhance the SQL abit more like for example, if the bookedTime is greater than current date, then it should not be selected. – Jul 31 '14 at 14:27
You can include that in the subquery (from the `in` clause) or just include it in the query you originally listed -- just be sure to put the join condition in the join clause, not the where clause. – Hambone Jul 31 '14 at 19:52
@Hambone Sorry but would you mind to show me some example? Cause I not sure what you meant by join condition. Because I tried to put WHERE exerciseID not in (SELECT exerciseID FROM bookedExercise) AND bookedTime > DATETIME('NOW') and it threw me an error message saying no such column bookedTime – Aug 01 '14 at 00:37
If you mean you ONLY want to exclude those with booked times > now, then you're really close. Keep the clause inside of the parentheses. where exerciseID not in (select exerciseID from bookedExercise where bookedTime > DATETIME('NOW')). – Hambone Aug 01 '14 at 01:16

score 1 · Answer 2 · answered Jul 31 '14 at 06:50

When you are using LEFT JOIN, you must put the join condition into the ON clause:

SELECT exercise.exerciseID,
       exercise.exerciseType,
       exercise.amout
FROM exercise         /* !! */
LEFT JOIN bookedExercise ON exercise.exerciseID = bookedExercise.exerciseID
WHERE bookedExercise.exerciseID IS NULL

score 0 · Answer 3 · answered Jul 31 '14 at 02:02

Your SQL looked okay... I think the problem might be you have a datatype mismatch. You have exercise ID as an integer in one table and text in another.

Also, if you have huge data volumes, you may want to consider an anti-join:

select
  e.*
from
  exercise e
where not exists (
  select 1
  from bookedExercise be
  where
    e.excerciseId  = be.exerciseID
)

-- edit --

On second glance, your SQL was not okay. You had your join condition in the where clause. This little change would fix your existing SQL:

SELECT exercise.excerciseId , exercise.exerciseType , exercise.amout
FROM exercise LEFT JOIN bookedExercise on
    exercise.excerciseId  = bookedExercise.exerciseID
WHERE bookedExercise.exerciseID IS NULL

SQLite query select all records that does not exist in another table

3 Answers3

Linked