Does not exist in one table

Question

I want to select tests that run on IE7 and not run on IE8. I tried this, but I get 0, and it is not.

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

Thanks!

Answer 1

try it

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

Answer 2

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 little rusty, but I think it does. The important thing is that you said, “I want all test_names with version“ ie7 ”, and also that there is no row in the database at all with version“ ie8 "" :))

Answer 3

This is a more optimal form of a valid request:

  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 that I added a comparison check for test_name, since without it you say that you will get all tests for ie7 only if there are no ie8 tests.

Subqueries are less efficient than left joins, and this IS NULL state check will give the same result and allow faster processing with a good index.

Answer 4

There is no condition in the subquery that the rows match the external SELECT element, for example:

 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'); 

Answer 5

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