CODEWITHSUNDEEP
sqlsql-serverselectequality
dumbledad
dumbledad

Reputation: 17525

Using an equality check between columns in a SELECT clause

I am using Microsoft SQL Server 2012 and I would like to run this seemingly simple query:

SELECT 
    FirstEvent.id AS firstEventID, 
    SecondEvent.id AS secondEventID, 
    DATEDIFF(second, FirstEvent.WndFGEnd, SecondEvent.WndFGStart) AS gap, 
    FirstEvent.TitleID = SecondEvent.TitleID AS titlesSameCheck
FROM VibeFGEvents AS FirstEvent
RIGHT OUTER JOIN VibeFGEvents AS SecondEvent
ON 
    FirstEvent.intervalMode = SecondEvent.intervalMode 
    AND FirstEvent.id = SecondEvent.id - 1 
    AND FirstEvent.logID = SecondEvent.logID

However FirstEvent.TitleID = SecondEvent.TitleID AS titlesSameCheck in the SELECT clause is incorrect syntax. But the SELECT Clause (Transact-SQL) documentation includes this syntax:

SELECT [ ALL | DISTINCT ]
[ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ] 
<select_list> 
<select_list> ::= 
    { 
        * 
        | { table_name | view_name | table_alias }.* 
        | {
            [ { table_name | view_name | table_alias }. ]
                { column_name | $IDENTITY | $ROWGUID } 
            | udt_column_name [ { . | :: } { { property_name | field_name } 
            | method_name ( argument [ ,...n] ) } ]
            | expression
            [ [ AS ] column_alias ] 
            }
        | column_alias = expression 
    } [ ,...n ]

I think that means an expression is valid in the select clause and indeed the examples given include things like 1 + 2. Looking at the documentation for expressions:

{ constant | scalar_function | [ table_name. ] column | variable 
    | ( expression ) | ( scalar_subquery ) 
    | { unary_operator } expression 
    | expression { binary_operator } expression 
    | ranking_windowed_function | aggregate_windowed_function
}

boolean equality checks are valid expressions and indeed the example expression given in the = (Equals) (Transact-SQL) documentation includes one:

SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE GroupName = 'Manufacturing'

albeit in the WHERE clause not the SELECT clause. It looks like I cannot use = the equality operator to compare expressions in my SELECT clause as they are being wrongly interpreted as assignment.

How do I include a Boolean equality column comparison equivalent to FirstEvent.TitleID = SecondEvent.TitleID AS titlesSameCheck in my SELECT clause?

Upvotes: 41

Views: 43082

Answers (3)

pyrospade
pyrospade

Reputation: 8078

You cannot use the Boolean type directly except in conditional statements (case, where, having, etc.)

Best way to solve your problem is to do something like

select case when x = y then 'true' else 'false' end

The bit type is probably the closest to boolean.

select CAST(case when x = y then 1 else 0 end as bit)

Of course, use whichever two values best represent what you are after.

Upvotes: 21

Allan
Allan

Reputation: 17429

As the two existing answers state, boolean values can't be returned as a column value. This is documented in the Comparison Operators section:

Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.

Given that restriction, using CASE to transform the value to something that can be displayed is your best alternative.

Upvotes: 2

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

Like this:

case when FirstEvent.TitleID = SecondEvent.TitleID then 1 else 0 end as titlesSameCheck

Upvotes: 51

Related Questions