Does 1 always equal '1' in SQL?

Question

I am trying to determine that standard SQL behaviour for comparing a number to a character or string version of the same number. Does SELECT 1 = '1' (or the like) always return some sort of "truthy" value (true, 1, 't', etc.)? I have confirmed as much on PostgreSQL and MySQL, but I cannot find a resource for SQL as a whole.

Update: The purpose for the question is that I'm trying to figure out if using a number, without the quotes, will work when selecting/inserting/updating/etc. from a non-numeric field whose value is a number.

Answer 1

1 is an Number and '1' is a CHAR array of some sort, they should never be equal. If they are that is an implementation dependent behavior

Answer 2

SELECT 1='1' gives TRUE since '1' is a correct constructor for INT in all implementation known to me.

But SQL uses strict typing, see that:

# SELECT 1=CAST('1' AS TEXT);
ERROR:  operator does not exist: integer = text
LINE 1: SELECT 1=CAST('1' AS TEXT);
                ^
HINT:  No operator matches the given name and argument type(s). You might need to add  explicit type casts.

Regarding the standard (SQL 92, 99 & 2003) it seems to be wrong:

     <literal> ::=
            <signed numeric literal>
          | <general literal>

     <general literal> ::=
            <character string literal>
          | <national character string literal>
          | <bit string literal>
          | <hex string literal>
          | <datetime literal>
          | <interval literal>

     <signed numeric literal> ::=
          [ <sign> ] <unsigned numeric literal>

     <unsigned numeric literal> ::=
            <exact numeric literal>
          | <approximate numeric literal>

     <exact numeric literal> ::=
            <unsigned integer> [ <period> [ <unsigned integer> ] ]
          | <period> <unsigned integer>

     <unsigned integer> ::= <digit>...

     <character string literal> ::=
          [ <introducer><character set specification> ]
          <quote> [ <character representation>... ] <quote>
            [ { <separator>... <quote> [ <character representation>... ] <quote> }... ]

because <quote> is only contained in <bit string literal>, <hex string literal>, ... but not in numeric literals...

Answer 3

SQL Server

if 1 = '1'
print 'yes'
else
print 'no'

output: yes

This gets converted, see here for a whole list of implicit and explicit conversion possibilities: CAST and CONVERT (Transact-SQL)

Answer 4

For an "always true" select statement simply use SELECT 1. That will always be true.

Answer 5

Testing from MySQL 5.x and SQL Server 2005, they both perform implicit conversion of '1' into 1 for the evaluation to return true.

But that could also have to do with collation.

Answer 6

First off, in SQL Server SELECT 1 = '1' isn't valid. Although, if you run the following code, you'll find that 1 does = '1'

if (1 = '1') begin
    print 'true'
end else begin
    print 'false'
end

results:

true

Answer 7

"SQL in general" does not have concept of a "truthy" value.

Unlike MySQL and PostgreSQL, in Oracle and SQL Server, no internal datatypes can be used as boolean values in WHERE clauses or WHEN predicates.

You should always have some kind of a predicate to use in these clauses.

No datatype can be used in mydata to make these queries work:

SELECT  1
WHERE   @mydata

or

SELECT  1
FROM    dual
WHERE   :mydata

Also, no SQL standard prescribes the type casting order.

The datatype of the constant can be casted to that of that of the column datatype or vice versa.

This can lead to the problems similar to those described in this question.

Answer 8

Although it appears to work in many implementations, per SQL standard, the comparison 1 = '1' is not allowed.

Answer 9

The question (given the update text) is not if:

SELECT 1 = '1'

will work, but will:

SELECT '1'::text = 1

work. Which is of course: no. At the very least on PostgreSQL, and for a really good reason.

Answer 10

From the Update:

Your update sounds like you want to do the following:

SELECT *
FROM MyTable 
WHERE StringColumn = 1

That will not work. If you have ANY values in that string column which are non-numeric, as soon as the sql engine gets to that row it will throw an error. In MS SQL Server the error is "Conversion failed when converting the varchar value 'blah' to data type int."

So, if you want to do the comparison, you would have to make sure you are comparing like data types. For example:

SELECT *
FROM MyTable 
WHERE StringColumn = '1'