More efficient double coalesce join alternative

Question

I have a procedure with a (slightly more complex) version of the below:

CREATE PROC sp_Find_ID (
    @Match1 varchar(10),
    @Match2 varchar(10)
) AS

DECLARE @ID int

SELECT @ID = ID
FROM Table1
WHERE Match1 = @Match1
    AND Coalesce(Match2,@Match2,'') = Coalesce(@Match2,Match2,'')

SELECT @ID ID

Essentially Match1 is a mandatory match, but Match2 is both optional on the input to the procedure, and on the table being searched. The 2nd match succeeds where the input and/or the table Match2 values are null, or where they're both the same (not null) value.

My question is: Is there a more efficient (or even more readable) way of doing this?

I've used this method a few times, and I feel slightly sullied each time (subjective dirtiness admittedly).

score 4 · Accepted Answer · edited May 23 '17 at 10:29

4

Is there a more efficient (or even more readable) way of doing this?

The example you provided, using COALESCE/etc is non-sargable. You need to separate things so only what needs to be present in the query is run:

DECLARE @ID int

IF @Match2 IS NOT NULL
BEGIN

  SELECT @ID = t.id
    FROM TABLE1 t
   WHERE t.match1 = @Match1
     AND (t.match2 = @Match2 OR t.match2 IS NULL)

END
ELSE
BEGIN

  SELECT @ID = t.id
    FROM TABLE1 t
   WHERE t.match1 = @Match1

END

SELECT @ID ID

If you want this to occur in a single SQL statement, dynamic SQL is the only real alternative. I highly recommend reading The curse and blessing of dynamic SQL before reading further:

DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N' SELECT @ID = t.id
                    FROM TABLE1 t
                   WHERE t.match1 = @Match1 '

    SET @SQL = @SQL + CASE 
                        WHEN @Match2 IS NOT NULL THEN
                          ' AND (t.match2 = @Match2 OR t.match2 IS NULL) ' 
                        ELSE 
                          ' '
                      END

BEGIN

  EXEC sp_executesql @SQL,
                     N'@ID INT OUTPUT, @Match1 VARCHAR(10), @Match2 VARCHAR(10)',
                     @ID, @Match1, @Match2

END

edited May 23 '17 at 10:29

Community

1
1

answered Sep 29 '10 at 17:20

OMG Ponies

325,700
82
523
502

The `Match1 = @Match1` bit is sargable though so dependant on how selective that is that could mitigate this. – Martin Smith Sep 29 '10 at 17:22
Very interesting, I may have to revise some of my previous query design. – Stu Pegg Sep 29 '10 at 17:23
The Match2 part is likely to be on less than 30 rows, whereas Match1 is ~ 50000. – Stu Pegg Sep 29 '10 at 17:23
@Martin Smith: Yeah, the match1 stuff is sargable, it's when using an IS NULL/COALESCE/ISNULL in brackets that things get non-sargable. – OMG Ponies Sep 29 '10 at 17:27
@OMG Assuming a composite index on `match1, match2` SQL Server would do a seek in on `match1` but then have to scan the 30 matching rows to evaluate the second part. Over time could add up (+1) – Martin Smith Sep 29 '10 at 17:28

gbn · Answer 2 · 2011-03-24T11:11:53.113

Avoiding OR and ISNULL etc

The EXCEPT bit returns no rows if either side IS NULL
Match2 <> @Match2 means exclude non-NULL non-matching

Something like this

DROP TABLE dbo.Table1

CREATE TABLE dbo.Table1 (ID int NOT NULL, Match1 int NOT NULL, Match2 int NULL)
INSERT dbo.Table1 VALUES (1, 55, 99), (2, 55, NULL)

DECLARE @Match1 int = 55, @Match2 int

SELECT ID
FROM
    (
    SELECT ID FROM Table1 WHERE Match1 = @Match1
    EXCEPT -- @Match2 = NULL, match both rows (99, NULL)
    SELECT ID FROM Table1 WHERE Match2 <> @Match2
    ) foo

SET @Match2 = -1
SELECT ID
FROM
    (
    SELECT ID FROM Table1 WHERE Match1 = @Match1
    EXCEPT -- @Match2 = -1, match ID = 2 only where Match2 IS NULL
    SELECT ID FROM Table1 WHERE Match2 <> @Match2
    ) foo

score 1 · Answer 3 · answered Sep 29 '10 at 17:07

1

Don't know if this is any more preferable.

SELECT @ID = ID
FROM Table1
WHERE Match1 = @Match1
    AND ((Match2 = @Match2) OR Coalesce(Match2,@Match2) IS NULL)

answered Sep 29 '10 at 17:07

Martin Smith

438,706
87
741
845

Won't "Match2 = @Match2" null the whole clause if either are null? – Stu Pegg Sep 29 '10 at 17:10
No. `unknown or true` = `true` and `unknown or false` = `unknown`. [3 valued logic truth table](http://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/) – Martin Smith Sep 29 '10 at 17:12
Null logic is even more mind boggling than I at first feared. – Stu Pegg Sep 29 '10 at 17:15
It makes sense though. If part of an `or` is true then it doesn't matter if the rest is true or false we know the statement as a whole is true. – Martin Smith Sep 29 '10 at 17:18
Presumably there's a minor speed improvement in using IsNull instead of a two-value Coalesce? – Stu Pegg Sep 29 '10 at 17:19

Nix · Answer 4 · 2010-09-29T17:15:24.077

0

Seems simple to me? I must be missing something.. you dont need the Coalesce

SELECT @ID = ID
 FROM Table1
 WHERE Match1 = @Match1
    AND (
          (Match2 is null and  @Match2 is null)
           or
           @Match2=Match2
    )

SELECT @ID ID

edited Sep 29 '10 at 17:15

answered Sep 29 '10 at 17:03

Nix

57,072
29
149
198

score 0 · Answer 5 · answered Sep 29 '10 at 17:07

0

I would have thought this should do it - providing that the @Match2 value will be NULL if it is optional.

CREATE PROC sp_Find_ID (
    @Match1 varchar(10),
    @Match2 varchar(10)
) AS

DECLARE @ID int

SELECT @ID = ID
FROM Table1
WHERE Match1 = @Match1
    AND Match2 = IsNull(@Match2, Match2)

SELECT @ID ID

answered Sep 29 '10 at 17:07

codingbadger

42,678
13
95
110

4

If `Match2` is null then `NULL = NULL` is unknown not true. – Martin Smith Sep 29 '10 at 17:08
@Martin - ah yes good point. This solution requires Match2 to not be Null – codingbadger Sep 29 '10 at 17:13

More efficient double coalesce join alternative

5 Answers5