not exists in the same table

I want to select the tests that run on IE7 and not run on IE8. I tried this but i get 0 and this is not true.

SELECT test_name开发者_StackOverflow社区
FROM tests 
WHERE version='ie7' 
AND NOT EXISTS (SELECT test_name FROM tests where version='ie8');

Thank you!

You probably mean:

SELECT test_name
FROM tests t1
WHERE version='ie7' 
AND NOT EXISTS (SELECT test_name FROM tests t2 where test_name = t1.test_name AND version='ie8');

(My Transact-SQL is a bit rusty, but I think this is how it is done. The important thing is that you were saying "I want all test_names with version 'ie7' and also that no row exists in the database at all with version 'ie8'" :) )

Try this

SELECT test_name
FROM tests 
WHERE version='ie7' 
AND test_name  NOT In (SELECT test_name FROM tests where version='ie8');

This is a more optimal form of the correct query:

 SELECT tests.test_name
 FROM tests
 LEFT JOIN tests AS tests2 ON tests.test_name = tests2.test_name
     AND tests2.version = 'ie8'
 WHERE tests.version = 'ie7' AND tests2.version IS NULL

You see I've added a comparison check for the test_name, as without it, you are saying get all tests for ie7 only if there are no ie8 tests at all.

Subqueries are less efficient than left joins, and this IS NULL condition check will produce the same result, and will allow for quicker processing with a good index.

You're missing a condition on the subquery to match rows to the outer SELECT e.g.:

SELECT t.test_name
FROM tests t
WHERE t.version='ie7' 
    AND NOT EXISTS (
        SELECT test_name FROM tests where test_name=t.test_name AND version='ie8');

   SELECT test_name
     FROM tests 
    WHERE version = 'ie7' 
   EXCEPT 
   SELECT test_name
     FROM tests 
    WHERE version = 'ie8';