More efficient double coalesce join alternative?

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.

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.

1 goes to look up "sargable" – Stuart Pegg Sep 29 '10 at 17:21 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. – Stuart Pegg Sep 29 '10 at 17:23 The Match2 part is likely to be on less than 30 rows, whereas Match1 is ~ 50000.

– Stuart 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.

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.

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).

– Stuart Pegg Sep 29 '10 at 17:10 No. Unknown or true = true and unknown or false = unknown.3 valued logic truth table – Martin Smith Sep 29 '10 at 17:12 Null logic is even more mind boggling than I at first feared. – Stuart 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? – Stuart Pegg Sep 29 '10 at 17:19.

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.

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 – Barry Sep 29 '10 at 17:13.

I must be missing something.. you don't 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.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.