MySQL query - show results that are not in another table

I have a query like this:

SELECT lesson.id, p1.first_name, p1.surname, start_time, instrument.name 
FROM  lesson, person AS p1, person AS p2, instrument, invoice_lesson
WHERE lesson.student = p1.id
AND   lesson.teacher = p2.id
AND   instrument.id  = lesson.instrument_id
ORDER BY surname

However, I would like to modify it so that it only shows results where lesson.id is not in the table invoice_lesson.lesson_id. Is this a correlated query? How do I do this?

From stackoverflow

• The easiest way:

  SELECT lesson.id, p1.first_name, p1.surname, start_time, instrument.name 
  FROM  lesson, person AS p1, person AS p2, instrument, invoice_lesson
  WHERE lesson.student = p1.id
  AND   lesson.teacher = p2.id
  AND   instrument.id  = lesson.instrument_id
  AND   lesson.id NOT IN (SELECT lesson_id FROM invoice_lesson)
  ORDER BY surname
  

  Might not exactly be the quickest one :)

  Tomasz Kopczuk

  thedz : You really should be using a join, rather than a sub select.

• You can do this with an outer join:

  SELECT lesson.id, p1.first_name, p1.surname, start_time, instrument.name 
  FROM  lesson l JOIN person p1 ON l.student = p1.id
  JOIN person p2 ON l.teacher = p2.id
  JOIN instrument i ON i.id = l.instrument_id
  LEFT JOIN invoice_lesson il ON l.id = il.lesson_id
  WHERE il.lesson_id IS NULL
  ORDER BY surname
  

  This approach will be much faster than the correlated subquery approach.

  Robert : the first line needed altering to match you aliases though ;) thx

• Try using a JOIN:

   SELECT lesson.id, p1.first_name, p1.surname, start_time, instrument.name 
   FROM  lesson, person AS p1, person AS p2, instrument, invoice_lesson
   JOIN  invoice_lesson
   ON    lession.id = invoice_lession.lesson_id
   WHERE lesson.student = p1.id
   AND   lesson.teacher = p2.id
   AND   instrument.id  = lesson.instrument_id
   ORDER BY surname
  

  Robert : Syntax error or access violation: 1066 Not unique table/alias: 'invoice_lesson'