Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

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

  1. What is 1=1 and null=null and why are we using these?
  2. What is difference between first query and second query?
like image 586
Manoj Avatar asked Dec 20 '22 07:12

Manoj


1 Answers

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)

like image 162
Fabian Barney Avatar answered Jan 08 '23 03:01

Fabian Barney