Using an equality check between columns in a SELECT clause

Question

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?

Answer 1

Like this:

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