Solved

why 1=1 is true in sql

Posted on 2013-01-30
18
461 Views
Last Modified: 2013-02-19
why 1=1 is true in sql ,mysql or oracle.
0
Comment
Question by:Insoftservice
  • 5
  • 4
  • 4
  • +5
18 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38834424
hmmm.. 1=1 is true right ? and hence obviously right.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38834427
that is a logical condition which evaluates to true and hence the result is true
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 50 total points
ID: 38834433
Usually, developers will use the 1 = 1 condition if they are not sure about the list of conditions that will added to that particular WHERE Clause. So for all the dynamic WHERE conditions that they will add, they can simply add

WHERE 1 = 1
AND col = ?
AND col2 = ?


something like that.
If 1 = 1 is not there, then they need to worry about WHERE to add the first AND clause..
0
 
LVL 36

Assisted Solution

by:Loganathan Natarajan
Loganathan Natarajan earned 50 total points
ID: 38834446
mostly, it is tried in sqlinjections
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 38834448
ya even i know that. But whats the reason behind it .
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38834453
It is a just a matter of practice while writing the dynamic sql stuff.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 100 total points
ID: 38834454
But it is not mandatory to have where 1=1 for all your dynamic sql statement which are prepared/dynamically executed.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 38835030
>>ya even i know that. But whats the reason behind it .

A where clause as just a bunch of checks that result in boolean (true/false) results.  Everything in a where clause evaluates to either true or false.

1=1 always evaluates to true.  You can also see 1=2 which forces a false.

Like others have mentioned, it is typically a shortcut for dynamic SQL where the executed statement must be syntactically correct.

Think of a report generating screen that has many report options.  You can hard-code the main select:
"select col1,col1,col2 from someTable where 1=1".

If no options are selected for the report, the report will return ALL rows.  If they select one or more options, the programmer can just append "AND" clauses to the sql string and it is still valid sql.

Say the user chooses a color from a dropdown list and a number in an age field.  The programmer just adds the following string to the hard-coded SQL:  "and color='Blue' and age=69".

then the report executes:
"select col1,col1,col2 from someTable where 1=1 and color='Blue' and age=69"
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38835500
Reason behind it??? 1 is 1. And 1 is 1. Hence 1=1

1 = 1 not only when it comes to databases
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 50 total points
ID: 38835948
When building a dynamic SQL, you only have ONE "WHERE" condition to start the "WHERE clause" followed by a bunch of ANDs & ORs conditions. It's easier if you set the WHERE condition to 1=1 and be over with it. Otherwise you'll have to code a more complicated logic to obtain a well written SQL statement.

Take a look at the following examples..

1. This one doesn't implement the "WHERE 1=1" shortcut:

DECLARE

l_sql           VARCHAR(32767);
l_select_clause VARCHAR(32767) := 'SELECT * FROM DUAL' || chr(10);
l_where_clause  VARCHAR(32767);

l_check_one boolean;
l_check_two boolean;

BEGIN

IF SYSDATE = CURRENT_DATE THEN
  l_where_clause := l_where_clause || 'WHERE dummy = ''X''' || chr(10);
  l_check_one := TRUE;
END IF;

IF USER = USER THEN
  IF l_check_one = TRUE THEN
  l_where_clause := l_where_clause || 'AND dummy = ''Y''' || chr(10);
  ELSE
  l_where_clause := l_where_clause || 'WHERE dummy = ''Y''' || chr(10);
  END IF;
  l_check_two := TRUE;
END IF;

l_sql := l_select_clause || l_where_clause;

dbms_output.put_line(l_sql);

END;
/


2. With the WHERE 1=1 shortcut:

DECLARE

l_sql           VARCHAR(32767);
l_select_clause VARCHAR(32767) := 'SELECT * FROM DUAL' || chr(10);
l_where_clause  VARCHAR(32767) := 'WHERE 1=1' || chr(10);

BEGIN

IF SYSDATE = CURRENT_DATE THEN
l_where_clause := l_where_clause || 'AND dummy = ''X''' || chr(10);
END IF;

IF USER = USER THEN
l_where_clause := l_where_clause || 'AND dummy = ''Y''' || chr(10);
END IF;

l_sql := l_select_clause || l_where_clause;

dbms_output.put_line(l_sql);

END;
/
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 total points
ID: 38835998
>>l_check_two boolean;

FYI:
This can be simplified somewhat but still has additional processing you need to worry about when doing dynamic SQL...

A single boolean will suffice:
already_has_where_clause boolean;

Then you can do:

l_where_clause := l_where_clause || case when already_has_where_clause then ' AND ' else ' WHERE '  end ||  dummy = ''X''' || chr(10);
--resetting to true even if already true doesn't matter
already_has_where_clause := true;
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 38836192
frnds i don't want query or how to write the query but what exactly i want is that why only 1=1 and 2=2 .

Till now what i have came to know is that select * from table where 1=1 .
Is more optimized  as compared to select * from table as it says database to render the whole table only once.
secondly there is something related with HEX value which i did not understood properly.

I hope now you might come to know my exact question.

For all simple or small steps we use to write to optimize our code or to solve our issue has something bigger reason behind it.
As a developer i know how to use it and why but what actually logic runs behind i wanted to know that
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 38836232
>>Is more optimized  as compared to select * from table as it says database to render the whole table only once.

Do you have a reference source for this?

My guess is if this is true for one version of one database product it is likley specific to that database.

Oracle doesn't care.  The optimizer is smart enough to not pay attention to it.

You can test it:
explain plan for
select * from dba_objects;

select * from table(dbms_xplan.display);

explain plan for
select * from dba_objects where 1=1;

select * from table(dbms_xplan.display);

Open in new window

0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 50 total points
ID: 38837052
Hi,

why 1=1 is true in sql ,mysql or oracle.
SQL obey to algebra postulates such as:
Reflexive Property of Equality: a = a

Is more optimized  as compared to select * from table as it says database to render the whole table only once.
What do you mean by render only once ? 1=1 changes nothing logically not physically.
secondly there is something related with HEX value which i did not understood properly.
Please, what kind of answer are you looking for ?



Regards,
Franck.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 100 total points
ID: 38837694
I have not seen or heard this --> " Till now what i have came to know is that select * from table where 1=1 . Is more optimized  as compared to select * from table as it says database to render the whole table only once. "

I believe this is not true otherwise all people/business users in this world want their applications/queries to run even faster and each query in that case would have had 1=1 , 2=2 or n = n whatever but that is not right. so in simple, dynamic sql statements have that 1=1 ( an equality condition in the beginning of the where clause for the above all said reasons.

Thanks,
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 38904422
i am unable to search the article where it had been specified about it.
But i hope its better to close these question .
Please suggest
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 38905268
I believe we have answered the question:  None of us think 1=1 helps with performance.

If you have anything else for us then we'll be happy to see what we can do.  If not, please close the question.

If you ever come across the source that stated 1=1 does help with performance you can post it here and I can re-open the question for continued discussion or you can ask a new question.
0
 
LVL 15

Author Closing Comment

by:Insoftservice
ID: 38908275
thx
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now