Geek Answers Handbook

MySQL: select problems

They have two tables:

wes - contains 8000 rows, with 2000 distinct masternames (mastername is a column in wes)
wem - contains 2100 rows, with 2100 distinct names (name is a column in wem)

I am trying to get names that exist in wem, but not like masternames in wes:

SELECT name FROM wem WHERE name NOT IN (SELECT DISTINCT mastername FROM wes)

But for some reason, my query does not return any strings, even if there are ~ 100 names in wem that do not exist as masternames in wes.

Any ideas on what's going wrong?

Thank!

+4
source share
2 answers

Are you sure there are no NULLS in the subsample?

Try SELECT mastername FROM wes WHERE mastername IS NULL

If there is, your query will return 0 rows.

Set to:

SELECT NAME FROM wem
WHERE NAME NOT IN (SELECT mastername FROM wes WHERE mastername IS NOT NULL)
+1
source

I usually avoid use NOT INin such a situation; try the query:

SELECT NAME
FROM   wem a
WHERE  NOT EXISTS(SELECT 1
                  FROM   wes b
                  WHERE  a.name = b.mastername)
0
source

More articles:


All Articles