Search code examples
sqlsql-serverdatabasesql-server-2014-express

Why is SQL ignoring the conditions in my WHERE clause?

For some reason, when I pass the following query:

SELECT [313], [313 DE MINIMIS LIMIT] 
FROM [Chem CAS INV] 
WHERE [313 DE MINIMIS LIMIT] IS NULL AND 313 = '313'

SQL Server is returning rows that clearly don't match the conditions specified, as shown below:

enter image description here

I know that sometimes working with NULL can require a more in depth understanding of how SQL works (like when using IS NOT NULL with IN statements vs. NOT EXISTS, etc.), but I've done simple checks with NULL like this many times, and I can't understand why 313 would ignore a simple check for a string value.

I'm using SQL Server 2014 Express, and here are the datatypes that go with these columns:

enter image description here

I feel like I'm missing something obvious, but I can't understand what that would be.

Solution

  • 313 = '313' is matching becaue SQL is thinking you meant the NUMBER 313 instead of your column [313]. You need to wrap it in brackets like you did the rest of your query. As a side note, this is one reason why naming a column a NUMBER is completely insane. You're going to have these bugs CONSTANTLY. Rename it something sensible like Val_313 or something else. You should think of the brackets as SQL Servers way of saying: "Are you sure you want to do this?" 99.99% of the time your answer your should be: no. :)