sqlsqliteselectcs50

Inequality operator gives a different result than an OR result even though I logically think it should be the same

I have been solving problem set 0 from Harvard's CS50 for SQL online course. There is a question in it, which says:

In 7.sql, write a SQL query to count the number of players who bat (or batted) right-handed and throw (or threw) left-handed, or vice versa.

There are two columns in the players table called bats and throws.

I tried two approaches:

SELECT COUNT(id) FROM players
WHERE (bats = 'R' AND throws = 'L') OR (bats = 'L' AND throws = 'R');

which gives the correct result.

However, I also tried this:

SELECT COUNT(id) FROM players
WHERE throws<>bats AND throws IS NOT NULL AND bats IS NOT NULL;

which (according to me) logically, should give the same answer. Can someone explain what I am missing? Sorry if there are any formatting issues as this is my first question.

Solution

  • Had there only been two non-null options for bats and throws, your second query would have been equivalent to the first. However, there are three. There are also players who bat with both hands (i.e., have a bats value of 'B'):

    sqlite> SELECT DISTINCT bats FROM players WHERE bats IS NOT NULL;
R
L
B

    Similarly, there are switch pitchers, that have a throws value of 'S':

    sqlite> SELECT DISTINCT throws FROM players WHERE throws IS NOT NULL;
R
L
S