The definition says:
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.
Does this mean that no nulls will be included in this query?
SELECT Region
FROM employees
WHERE Region = @region
Or do ANSI_NULL
s concern only queries like this one (where the WHERE
includes the specific word NULL
)?
SELECT Region
FROM employees
WHERE Region = NULL
This question is related to
sql
tsql
stored-procedures
SET ANSI_NULLS ON
IT Returns all values including null values in the table
SET ANSI_NULLS off
it Ends when columns contains null values
If ANSI_NULLS is set to "ON" and if we apply = , <> on NULL column value while writing select statement then it will not return any result.
Example
create table #tempTable (sn int, ename varchar(50))
insert into #tempTable
values (1, 'Manoj'), (2, 'Pankaj'), (3, NULL), (4, 'Lokesh'), (5, 'Gopal')
SET ANSI_NULLS ON
select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (0 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (0 row(s) affected)
SET ANSI_NULLS OFF
select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (1 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (4 row(s) affected)
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
For e.g
DECLARE @TempVariable VARCHAR(10)
SET @TempVariable = NULL
SET ANSI_NULLS ON
SELECT 'NO ROWS IF SET ANSI_NULLS ON' where @TempVariable = NULL
-- IF ANSI_NULLS ON , RETURNS ZERO ROWS
SET ANSI_NULLS OFF
SELECT 'THERE WILL BE A ROW IF ANSI_NULLS OFF' where @TempVariable =NULL
-- IF ANSI_NULLS OFF , THERE WILL BE ROW !
Set ANSI NULLS OFF will make NULL = NULL comparision return true. EG :
SET ANSI_NULLS OFF
select * from sys.tables
where principal_id = Null
will return some result as displayed below: zcwInvoiceDeliveryType 744547 NULL zcExpenseRptStatusTrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525 NULL
While this query will not return any results:
SET ANSI_NULLS ON
select * from sys.tables
where principal_id = Null
If @Region
is not a null
value (lets say @Region = 'South'
) it will not return rows where the Region field is null, regardless of the value of ANSI_NULLS.
ANSI_NULLS will only make a difference when the value of @Region
is null
, i.e. when your first query essentially becomes the second one.
In that case, ANSI_NULLS ON will not return any rows (because null = null
will yield an unknown boolean value (a.k.a. null
)) and ANSI_NULLS OFF will return any rows where the Region field is null (because null = null
will yield true
)
I guess the main thing here is:
Never user:
@anything = NULL
@anything <> NULL
@anything != null
Always use:
@anything IS NULL
@anything IS NOT NULL
Source: Stackoverflow.com