views:

564

answers:

4

I have two columns that are joined together on certain criteria, but I would also like to check two see 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
+1  A: 

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.

Lucero
+5  A: 

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.

Ken White
I was looking for something short. CASE WHEN seems to be overly long for my taste.
Orion Adrian
I posted the actual CASE WHEN statement - how short do you need? SQL statement length differences of a few characters are meaningless, and being terse where you don't need to can make your code a lot harder to maintain later. Avoiding the proper construct to try and find one that's a few letters shorter to type is generally a bad idea.
Ken White
Consider using numbers and not characters: "CASE WHEN COLUMN1 = COLUMN2 THEN 1 ELSE 0 END AS MyDesiredResult", but I agree with Ken, this is the best choice.
Rob Garrison
@Rob: I figured Orion would know whether a 1 or a '1' would be more useful in the actual usage. :-)
Ken White
When you only want to define cases of 0 or 1, I usually use a BIT explicitly. Just do like: ... then cast (1 as bit) ...
@hainstech: That works, of course. But in the case where you're simply returning data for the user to view, say in a grid of some kind, does it really matter? You may cast to a bit, but the grid is still going to need a character to display (8 bits), and unless you're returning a ++whole lot++ of bit fields, the bandwidth savings will be less than the performance hit of the cast().
Ken White
I'm not trying to find some extremely terse way to say something. COLUMN1 = COLUMN2 AS ThirdColumn is sufficiently long to communicate th meaning. I find CASE WHEN for this scenario unwieldy.
Orion Adrian
I disagree. COLUMN1 = COLUMN2 AS ThirdColumn isn't nearly as undestandable to someone not familiar with the data, whereas the CASE WHEN is extremely readable and understandable. Not saying you're wrong, mind you; just that I don't agree. :-)
Ken White
A: 

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.

David Elizondo
See my answer that begins, "Regarding David Elizondo's answer".
Rob Garrison
You are right Rob. the ISNULL function can give false positives. It should return something that differentiate completely from the context of that two columns values. In my example I'm comparing varchar columns so no problem returning value an int 1. And as I said my solution doesn't return zero (0) but it creates a differentiation when the columns are/aren't equal. It is the closest solution I can think of if he doesn't want to use CASE :)
David Elizondo
A: 

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
Rob Garrison