Getting SQL Logic Correct

It has been a while since I have had to run an SQL query against a relational database. Today I worked with some of our developers to try and figure out why some data doesn't make sense. This requires running SQL queries and is something I actually enjoy creating. I saw one query and it just didn't look right. The query ran fine but I don't think it produced the expected results due to negative logic.

Imagine you have a table with the following 3 rows:

     Name
     -----
     Matt
     Rhod
     Felix

Suppose you want to query everyone, you would use the following SQL statement:

SELECT * FROM my_table;

Now suppose you wanted to query everyone except Matt, you would use:

SELECT * FROM my_table 
WHERE Name != 'Matt';

The WHERE clause acts like a filter and the exclamation point means NOT. You could also use:

SELECT * FROM my_table 
WHERE Name = 'Rhod' OR Name = 'Felix';

This eliminates the exclamation point but requires that you know who else is in the table. The query I looked at today combined the OR operator along with NOT. Unfortunately things get backwards and your expected results may not be what you want them to be. Supposed you have no idea what names are in the database table but you know you want to see everyone except Rhod and Felix. You might be tempted to use the following:

SELECT * FROM my_table
WHERE Name != 'Rhod' OR Name != 'Felix';

Unfortunately the query would return all of the rows in the table. Why? Because the database would go to the first row and see that Matt is neither Rhod nor Felix and return that row. It would go to the next row and look to make sure  that only one of the two conditions are met because the OR operator is used. Since the name does contain Rhod, it fails one test but Rhod is not Felix and so Rhod is returned in the query. The same logic holds true for Felix.

The fix is to replace the OR operator with the AND operator. The correct query should be:

SELECT * FROM my_table
WHERE Name != 'Rhod' AND Name != 'Felix';

The only row returned is Matt which is what you want.