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.

Logical Thinking

Today has been a very emotionally charged day. I was awoken early this morning with some relatively bad news from home. Nobody died or anything quite that tragic, but it still had me thinking with my emotions instead of logically. One nice thing about dealing with computers every day is how emotionless they are. One plus one will always equal two on a computer. When you are dealing with humans, sometimes you get different numbers and that can be confusing.

Unfortunately I am not the only one that thinks emotionally sometimes. I don't think politicians know how to make decisions that aren't emotionally based. That can explain all of the bickering that is taking place in the US congress right now. Sometimes I wish they would think like a computer every once and a while and make a decision based on logic instead of which party proposed the idea. A democrat or a republican could propose the perfect solution and the other party would vote it down simply because it came from the other side.

I was listening to a commentary on the radio just after Congress passed an emergency measure to keep the country from going off the fiscal cliff. If we go back to the original tax cuts that President Bush proposed, the democrats opposed them but they passed anyways. Now the democrats think they are a good idea and just couldn't agree with the republicans on which cuts to keep. From a logical perspective, it doesn't make sense.

Listening to all of the politicians in this country makes me glad that I work with computers. It provides my life with structure an logic. Now if we could just pass along some of that to Washington D. C. we might be able to solve some of the real problems facing our country. I'm not holding my breath though.