why 1=1 is true in sql

why 1=1 is true in sql ,mysql or oracle.
LVL 15
InsoftserviceAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
hmmm.. 1=1 is true right ? and hence obviously right.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
that is a logical condition which evaluates to true and hence the result is true
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
Loganathan NatarajanConnect With a Mentor LAMP DeveloperCommented:
mostly, it is tried in sqlinjections
0
 
InsoftserviceAuthor Commented:
ya even i know that. But whats the reason behind it .
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
It is a just a matter of practice while writing the dynamic sql stuff.
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
But it is not mandatory to have where 1=1 for all your dynamic sql statement which are prepared/dynamically executed.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>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
 
YZlatCommented:
Reason behind it??? 1 is 1. And 1 is 1. Hence 1=1

1 = 1 not only when it comes to databases
0
 
paquicubaConnect With a Mentor Commented:
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
 
InsoftserviceAuthor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>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
 
Franck PachotConnect With a Mentor Commented:
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
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
InsoftserviceAuthor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
InsoftserviceAuthor Commented:
thx
0
All Courses

From novice to tech pro — start learning today.