2

I have two tables: teacher_lm and teacher. These tables have both the column "teacher_email"

What I need is to get the emails that are present in teacher_lm but not in teacher.

I have 2 different approaches to solve this, but I don't understand why one of them doesnt give any result and the other one returns a lot of rows.

First one: 842 rows

SELECT   DISTINCT lm.teacher_email
FROM       teacher_lm as lm
WHERE      NOT EXISTS (SELECT  * FROM teacher as lt
       WHERE lt.teacher_email = lm.teacher_email         
      )

Second one: no results

SELECT DISTINCT lm.teacher_email FROM
teacher_lm AS lm
WHERE lm.teacher_email NOT IN
(SELECT lt.teacher_email FROM 
teacher AS lt)

Could you tell me what I'm doing wrong here, and what's the best way to do this?

Thank you.

Vika
  • 391
  • 5
  • 16

2 Answers2

6

The "in" subquery probably has a NULL in it.

Try this instead:

SELECT DISTINCT lm.teacher_email
FROM teacher_lm AS lm
WHERE lm.teacher_email NOT IN (SELECT coalesce(lt.teacher_email, '')
                               FROM  teacher AS lt)

By the way, I think the first version is the version recommended for mysql for optimization reasons.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you!! You were right :) Now it gave the same number of results. The IN subquery took years longer to finish than the EXIST one, so I'll go for that one. Thanks again :) – Vika Aug 15 '12 at 17:57
  • @Vika . . . just in case you ever use another database, the peculiarity with EXISTS being better than IN is specific to mysql. May or may not be true in other databases, but other database engines optimize IN and NOT IN better. – Gordon Linoff Aug 15 '12 at 18:55
1

Your first query is working on the existence of an entire row.

The second query may be returning nulls. I don't believe the NOT IN clause works well in the presence of nulls.

Gordon's answer corrects this by replacing nulls with an empty string.

mcarpenter
  • 111
  • 4