[sql] How do I compare two columns for equality in SQL Server?

I have two columns that are joined together on certain criteria, but I would also like to check if two other columns are identical and then return a bit field if they are.

Is there a simpler solution than using CASE WHEN?

Ideally I could just use:

    SELECT Column1 = Column2 AS MyDesiredResult
      FROM Table1
INNER JOIN Table2 ON Table1.PrimaryKey = Table2.ForeignKey

CASE WHEN is the better option

SELECT 
  CASE WHEN COLUMN1 = COLUMN2 
    THEN '1' 
    ELSE '0' 
  END 
  AS MyDesiredResult
FROM Table1
INNER JOIN Table2 ON Table1.PrimaryKey = Table2.ForeignKey

A solution avoiding CASE WHEN is to use COALESCE.

SELECT
    t1.Col2 AS t1Col2,
    t2.Col2 AS t2Col2,
    COALESCE(NULLIF(t1.Col2, t2.Col2),NULLIF(t2.Col2, t1.Col2)) as NULL_IF_SAME
 FROM @t1 AS t1
JOIN @t2 AS t2 ON t1.ColID = t2.ColID

NULL_IF_SAME column will give NULL for all rows where t1.col2 = t2.col2 (including NULL). Though this is not more readable than CASE WHEN expression, it is ANSI SQL.

Just for the sake of fun, if one wants to have boolean bit values of 0 and 1 (though it is not very readable, hence not recommended), one can use (which works for all datatypes):

1/ISNULL(LEN(COALESCE(NULLIF(t1.Col2, t2.Col2),NULLIF(t2.Col2, t1.Col2)))+2,1) as BOOL_BIT_SAME.

Now if you have one of the numeric data types and want bits, in the above LEN function converts to string first which may be problematic,so instead this should work:

1/(CAST(ISNULL(ABS(COALESCE(NULLIF(t1.Col2, t2.Col2),NULLIF(t2.Col2, t1.Col2)))+1,0)as bit)+1) as FAST_BOOL_BIT_SAME_NUMERIC

Above will work for Integers without CAST.

NOTE: also in SQLServer 2012, we have IIF function.

The use of IIF? And it depends on version of SQL Server.

SELECT
IIF(Column1 = Column2, 1, 0) AS MyDesiredResult
FROM Table;

Regarding David Elizondo's answer, this can give false positives. It also does not give zeroes where the values don't match.

Code

DECLARE @t1 TABLE (
    ColID   int     IDENTITY,
    Col2    int
)

DECLARE @t2 TABLE (
    ColID   int     IDENTITY,
    Col2    int
)

INSERT INTO @t1 (Col2) VALUES (123)
INSERT INTO @t1 (Col2) VALUES (234)
INSERT INTO @t1 (Col2) VALUES (456)
INSERT INTO @t1 (Col2) VALUES (1)

INSERT INTO @t2 (Col2) VALUES (123)
INSERT INTO @t2 (Col2) VALUES (345)
INSERT INTO @t2 (Col2) VALUES (456)
INSERT INTO @t2 (Col2) VALUES (2)

SELECT
    t1.Col2 AS t1Col2,
    t2.Col2 AS t2Col2,
    ISNULL(NULLIF(t1.Col2, t2.Col2), 1) AS MyDesiredResult
FROM @t1 AS t1
JOIN @t2 AS t2 ON t1.ColID = t2.ColID

Results

     t1Col2      t2Col2 MyDesiredResult
----------- ----------- ---------------
        123         123               1
        234         345             234 <- Not a zero
        456         456               1
          1           2               1 <- Not a match

I'd go with the CASE WHEN also.

Depending on what you actually want to do, there may be other options though, like using an outer join or whatever, but that doesn't seem to be what you need in this case.

The closest approach I can think of is NULLIF:

SELECT 
    ISNULL(NULLIF(O.ShipName, C.CompanyName), 1),
    O.ShipName,      
    C.CompanyName,
    O.OrderId
FROM [Northwind].[dbo].[Orders] O
INNER JOIN [Northwind].[dbo].[Customers] C
ON C.CustomerId = O.CustomerId

GO

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

So, above query will return 1 for records in which that columns are equal, the first expression otherwise.