Using an equality check between columns in a SELECT clause

equality, select, sql, sql-server

Solution

Like this:

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

Problem

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?

Original source