Solved

why 1=1 is true in sql

Posted on 2013-01-30
18
462 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

912 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

23 Experts available now in Live!

Get 1:1 Help Now