What is 'where 1=1' and 'where null=null' in sql server

Question

I have a table of some columns named as Students.

I am using following three queries to select rows.

 1. select * from Students
 2. select * from Students where 1=1
 3. select * from Students where null=null

When I execute first then this returns all rows.

When I execute second then this is also returns all rows.

But when I execute third then this does not return any row.

I have two issues

What is 1=1 and null=null and why are we using these?

What is difference between first query and second query?

http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server this may be help you to the right direction — Ameya Deshpande, Jan 31 '15 at 09:14
@jarlh http://stackoverflow.com/questions/9766717/in-sql-server-what-does-set-ansi-nulls-on-means this will help you — Ameya Deshpande, Jan 31 '15 at 09:42
@POHH it isn't `false` or `true`. SQL uses three valued logic and it is the third value of `unknown`. — Martin Smith, Jan 31 '15 at 12:02
@Martin Smith yes . the `unknown` will be the result and ultimate output will be `false` the condition will be `false` and will not return any `rows`. thats what i was trying to say. thanx for `correcting` — Ameya Deshpande, Jan 31 '15 at 12:04

score 4 · Accepted Answer · answered Jan 31 '15 at 10:00

To the questions part about "why" using this:
Since 1=1 always evaluates to true and true is the neutral element for logical AND operation, it is often used for dynamically built queries.

The problem with dynamically built queries that are using multiple AND operations added or not to the query on some conditions ist that you've to keep track about it is the first condition added or not. (i.e. SELECT * FROM mytable WHERE AND bla = 'blub' is not valid; we must suppress the AND with the first condition added to the query)

All this can be avoided when we change the queries base to SELECT * from mytable WHERE 1=1. Now we can add our AND conditions dynamically without considering if it is the first condition added or not. (i.e. SELECT * FROM mytable WHERE 1=1 AND bla = 'blub' is valid)

Along the same lines, I guess the use of the last query is to extend it with OR? — Vincent van der Weele, Jan 31 '15 at 10:09
@VincentvanderWeele Yes, because `false` is the neutral element for logical `OR` operation. But `null=null` is not ideal for various reasons. First of all it is "something special" which can be configured to evaluate to `true` or `false`. Second it is unintuitive that something identical aroung `=` evaluates to `false`. I would prefer using `1=0` over `null=null`in such cases where you need the neutral element for `OR` operation. — Fabian Barney, Jan 31 '15 at 10:14

score 2 · Answer 2 · edited May 23 '17 at 12:00

if you put ANSI_NULL off then it will return true . if it is on then it will return false

set ansi_nulls off

if null = null
    print 'true'
else
    print 'false'


set ansi_nulls ON

if null = null
    print 'true'
else
    print 'false'

reference got from Null=Null

@Jarlh if i set ansi_nulls off

when you set ansi_nulls off it will tell that not to follow ANSI standerds for comparing null values then this query will result all rows.

set ansi_nulls off

select * from cust_data where null=null

score 2 · Answer 3 · answered Jan 31 '15 at 09:58

1=1 is always true
null=null is always false

Usually one uses these constructs, when some code dynamically adds conditions to the where clause of a given query.

With where 1=1 the code can simply add restrictions with and ... without having to check whether this is the first restriction.

The same holds true with where null=null and or ...

Hint: this kind of coding comes usually with textual processing of SQL queries (instead of structural processing). Look out for SQL injections when you see such code!

score 1 · Answer 4 · answered Jan 31 '15 at 09:15

The first query simply returns all rows of the table.

The second query returns only rows that match the WHERE clause. Since this where clause is always true, it returns all rows.

The last query again returns only the rows that match the WHERE clause. Since this condition is always false, it doesn't return a row at all.

For the last query, you could instead try:

SELECT * 
FROM Students WHERE NULL IS NULL

The IS operator lets you compare NULL values, so this query again would return all rows. This is a general rule: never use = to check for NULL.

Ajay2707 · Answer 5 · 2015-01-31T10:01:02.753

This is the simplest logic as 1 = 1 always true and 2 null value are not same when you compare null value.

Where condition is used to filter the data by columns value and if we give the 1 = 1 , its logical filter, then return everything. and when we give 1=2 it gives nothing (just think why).

select * from Students
select * from Students where 1=1

So your first 2 query is same and that's why it give the same result.

select * from Students where null = null

Its obviously gives 0 row because both null is difference in terms of compare and the condition is not fullfill.

Try this and find out the logic why it gives as first 2 query.

select * from Students where isnull(null,'') = isnull(null ,'')

score 1 · Answer 6 · answered Jan 31 '15 at 09:58

First and Second gives same results.

But 1=1 and null=null are not same. 1=1 is always TRUE but null=null is not.

Null is "unknown" or "does not exist". In that case you cant judge the true of false condition.

If you want to make it true always, then you can use this

set ansi_nulls off

and check this

if null = null
    print 'true'
else
    print 'false'

and your third query also works by switching it to OFF. By default it is set to ON condition which always doesn't work

What is 'where 1=1' and 'where null=null' in sql server

6 Answers6