Does a SQL join only execute the minimum number of conditions?

Question

In C# if I run the following.

if(obj.a() && obj.b()){
    // do something
}

Function b will only execute if a returns true. Does the same thing happen below?

select
    *
from
    tablea a
    inner join tableb b
        isnumeric(b.col1) = 1
        and cast(b.col1 as int) = a.id

Will the cast only be executed when b.col1 is a numeric?

related: [Is the SQL WHERE clause short-circuit evaluated](http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated) — AakashM, Feb 28 '12 at 10:04

score 2 · Accepted Answer · answered Feb 28 '12 at 10:17

2

You can simulate short-circuit evaluation using a CASE expression.

ON CASE WHEN ISNUMERIC(b.col1) = 1
       THEN CAST(b.col1 AS int)
       ELSE NULL
   END = a.id

answered Feb 28 '12 at 10:17

Anthony Faull

17,549
5
55
73

1

+1 This is the only safe way of doing this. The `ELSE NULL` is redundant though. – Martin Smith Feb 28 '12 at 10:59

Tim Schmelter · Answer 2 · 2012-02-28T11:27:05.777

This covers short-circuat evaluation in SQL-Server deeply:

http://www.sqlservercentral.com/articles/T-SQL/71950/

In short: the evaluation order depends on the query optimizer.

Edit: As Martin commented this does not guarantee the order since it could also be optimized. From the above link(i should have read it completely):

When run against a SQL Server 2000, no error is thrown, but SQL Server 2005 and 2008 implement an optimization to push non-SARGable predicates into the index scan from the subquery which causes the statement to fail.

To avoid this issue, the query can be rewritten incorporating a CASE expression, maybe a bit obscure, but guaranteed not to fail.

So this should guarantee that ISNUMERIC will be evaluated first:

SELECT aData.*,bData.*
FROM #TableA aData INNER JOIN  #TableB bData
ON aData.id = CASE ISNUMERIC(bData.col1) WHEN 1 THEN CAST(bData.col1 AS INT) END

Ignore my first approach(which might not work everytime):

~~You should modify your join to ensure that is gets evaluated correctly~~:

SELECT aData.*,bData.*
FROM #TableA aData INNER JOIN 
(
    SELECT col1
    FROM #TableB b
    WHERE ISNUMERIC(b.col1) = 1
 ) AS bData
 ON aData.id = CAST(bData.Col1 AS int)

Sample data:

create table #TableA(id int)
create table #TableB(col1 varchar(10))

insert into #TableA values(1);
insert into #TableA values(2);
insert into #TableA values(3);
insert into #TableA values(4);

insert into #TableB values('1');
insert into #TableB values('2');
insert into #TableB values(null);
insert into #TableB values('4abc');

SELECT aData.*,bData.*
FROM #TableA aData INNER JOIN 
(
    SELECT col1
    FROM #TableB b
    WHERE ISNUMERIC(b.col1) = 1
 ) AS bData
 ON aData.id = CAST(bData.Col1 AS int)

drop table #TableA;
drop table #TableB;

Result:

id  col1
1    1
2    2

score 1 · Answer 3 · edited May 23 '17 at 11:55

From HERE:

No. Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.

Does a SQL join only execute the minimum number of conditions?

3 Answers3