How do I compare two columns for equality in SQL Server

Question

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

User · Answer

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  lt - Not a zero         456         456               1           1           2               1  lt - Not a match

User · Answer

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

User · Answer

What s wrong with CASE for this  In order to see the result  you ll need at least a byte  and that s what you get with a single character    CASE WHEN COLUMN1   COLUMN2 THEN  1  ELSE  0  END AS MyDesiredResult   should work fine  and for all intents and purposes accomplishes the same thing as using a bit field

User · Answer

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

User · Answer

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

User · Answer

The use of IIF  And it depends on version of SQL Server   SELECT IIF Column1   Column2  1  0  AS MyDesiredResult FROM Table

User · Answer

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

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

Code

Results

Examples related to sql

Examples related to sql-server

Examples related to tsql