SET ANSI_NULLS OFF
SELECT *
FROM (SELECT NULL AS ID, 'XYZ' AS NAME) TABLE1
INNER JOIN (SELECT NULL AS ID, 'ABC' AS NAME) TABLE2 ON TABLE1.ID = TABLE2.ID
After setting ANSII_NULLS OFF, I am not getting correct output
Asked
Active
Viewed 417 times
0
Mova
- 928
- 1
- 6
- 23
-
If you need this you can use `ON EXISTS (SELECT TABLE1.ID INTERSECT SELECT TABLE2.ID)` – Martin Smith Dec 20 '14 at 19:35
1 Answers
1
You are getting correct output. You just aren't getting the output you expected.
The documentation is quite clear that the ANSI_NULLS setting only applies to literal comparisons to NULL. From that page:
SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.