0

I have the below query in SQL server 2012 which is running fine when the whole query is run, but the inner query is not running individually because AdviceRecordID doesn't belong to tblB.

SELECT DISTINCT SubLOBID 
FROM tblA 
WHERE AdviceRecordID 
IN (SELECT AdviceRecordID
    FROM tblB
)

The first case, where the whole query is run, is not considering the WHERE condition at all and returing all the results without any error which is strange for me.

Ocaso Protal
  • 19,362
  • 8
  • 76
  • 83
Ajay Agrawal
  • 145
  • 2
  • 10

2 Answers2

1

This works as expected and required by the SQL standard. If a subquery references a column that is not available in the tables of the subquery, but is a valid column in the outer query, the value from the outer query's column is used.

So the query:

SELECT DISTINCT SubLOBID 
FROM tblA 
WHERE AdviceRecordID IN (SELECT AdviceRecordID
                         FROM tblB);

Is in fact a co-related subquery and is parsed and executed as:

SELECT DISTINCT SubLOBID 
FROM tblA 
WHERE AdviceRecordID IN (SELECT tblA.AdviceRecordID
                         FROM tblB);

So for each row in tblA the subquery returns the value of tblA.AdviceRecordID once for each row in tblB and compares that to the rules of the IN operator.

That's why the query as a whole is valid, and behaves like no where clause was used as the above is equivalent to:

SELECT DISTINCT SubLOBID 
FROM tblA 
WHERE AdviceRecordID IS NOT NULL;

And if there are no NULL values in the column AdviceRecordID then the WHERE clause is not filtering out anything.

  • So the trick is, SQL server doesn't parse each inner query individaully. It depends on the outer query as well. – Ajay Agrawal Feb 23 '18 at 08:48
  • @AjayAgrawal: a SQL query is always parsed as **one** (complete) statement. –  Feb 23 '18 at 08:49
  • I changed the column name in inner query and replaced with a column name which is not present in either of the tables. It is not parsing and throwig error. So your analysis is correct – Ajay Agrawal Feb 23 '18 at 08:50
0

Better to use EXISTS instead of IN clause.

Check the difference:

Difference1

Difference2

SELECT DISTINCT A.SubLOBID 
FROM tblA A
WHERE EXISTS 
(
    SELECT B.AdviceRecordID
    FROM tblB B
    WHERE B.AdviceRecordID=A.AdviceRecordID
)
Sandip - Frontend Developer
  • 14,397
  • 4
  • 35
  • 61
  • From the question: "*because AdviceRecordID doesn't belong to tblB*" so `SELECT B.AdviceRecordID` is invalid –  Feb 23 '18 at 07:44
  • In that case no need any subquery – Sandip - Frontend Developer Feb 23 '18 at 07:45
  • I prefer in because it's less code. I would only use exists if there were actually a performance issue personally. – BVernon Feb 23 '18 at 07:54
  • @SandipPatel: here the question is, why SQL server doesn't throw any error in the query mentioned. – Ajay Agrawal Feb 23 '18 at 08:52
  • @SandipPatel I disagree. In() and Exists() for logically equivalent queries produce more than often same query plan - and better is to use query syntax, corresponding more closely to problem description and therefore easier to understand and maintain. It was many years ago, when SQL optimizers didn't handle these cases similarly - nowadays I haven't seen differences in query plans. – Arvo Feb 23 '18 at 11:13
  • Try with sub query which return `NULL` , you may got difference between both – Sandip - Frontend Developer Feb 23 '18 at 11:15