Usage of single quotes in SQL queries

Question

I have the following SQL query:

SELECT id_zam AS test, 
     CASE WHEN (SELECT something FROM sth WHERE id_something = test) = 1 
          THEN 
              'true' 
          ELSE 
              'false'  
     END as test_result
FROM testTable

something equals 1. So the CASE statement is TRUE so it's going to execute THEN statement which is 'true'

But when I add single quotes around the alias test like this:

SELECT id_zam AS 'test', 
     CASE WHEN (SELECT something FROM sth WHERE id_something = 'test') = 1 
          THEN 
              'true' 
          ELSE 
              'false'  
     END as test_result
FROM testTable

The query returns 'false'. Can anyone explain why they're different?

Answer 1

The single quotes (or apostrophes, if you prefer) are used to represent strings (sometimes dates) but not integer values.

I assume your id_something is an integer variable. If that is the case, the problem lies in that you are trying to compare an integer to a string, which will always return false.

In the first example, you selected an integer and gave it the alias test. When you do your comparison, you're comparing two integers which is valid. However, once you change it to 'test' you now have a string, which is in no way related to the id_zam. When you do your comparison of an integer and a string, it returns false by default.

EDIT This is true for aliases, but not necessarily true all the time. In this example yes, test is an alias and 'test' is a string literal, so they will be treated differently. However, by MySQL defaults, single quotes won't affect the comparison of other items. For example, if you had a query with condition 1 = '1' this will return true. See the comments to this answer for another explanation.

To reemphasize, and simplify, don't use single quotations if you want to compare something using an alias.