Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

why 1=1 is true in sql

Posted on 2013-01-30
18
Medium Priority
?
473 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 200 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 36

Assisted Solution

by:Loganathan Natarajan
Loganathan Natarajan earned 200 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 400 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 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
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 200 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 800 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 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 200 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 400 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

772 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