Solved

where 1=1

Posted on 2014-09-09
7
349 Views
Last Modified: 2014-09-09
Can I ask what the value of the where clause WHERE 1=1 is in the below query? Tried a few articles but it still isn't that clear. What exactly does it bring to the query, or perhaps put another way – what data will be removed if you remove the 1=1 from the where clause. Please keep answers basic as still trying learning SQL. This is in Oracle 11g.

SELECT aip.*
FROM ap_invoice_lines_all aila ,ap_invoice_payments_all aip
WHERE 1=1
AND aila.invoice_id=aip.invoice_id
AND aila.po_line_id is null
0
Comment
Question by:pma111
7 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 125 total points
ID: 40311686
"1=1" is just another conditional expression like "aila.invoice_id = aip.invoice_id", no rocket science at all ;-)
BUT, do not get used to this style in production code/SQL!!

Some people use this "style" to have their where clauses get concatenated whenever they need dynamically created SQL statements in their apps.
On the other hand, if you test or create SQL statements (and depending on your IDE/editor), commenting and un-commenting lines (in the where clause) is somewhat "faster", but anyways: as soon as this code/SQL gets moved to production, get rid of that "1=1"!!!

Sidenote: a quick way to clone a table's structure (NO data) is the use of "1=2" like this:
create table clone_of_table1 as select * from table1 where 1=2

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 40311690
actually, I agree on the 1=1 is used when applications build the where clause dynamically, where indeed it becomes very simple to add conditions with
set @sql = ' AND xxxxx '

if the @sql was set before to 'SELECT ... FROM .. WHERE 1 = 1 '
if that part was not set, you would have to handle each additional condition to check if the WHERE part is already there or not...

commenting conditions (for debugging) becomes very easy...

this said: this should only appear in dynamic SQL, while dynamic sql should be really the exception
0
 
LVL 4

Assisted Solution

by:jtrifts
jtrifts earned 125 total points
ID: 40311699
There is no immediate value to this in a sql statement such as this one.
 where 1=1, like while 1=1, is essentially a Boolean comparison operation which will always evaluate to true.
You can compare sql plans -- and the plan will look the same.
You can trace it / tkprof it, and overall results will be similar.

However, where we sometimes see 1=1 is as part of a sql injection attack (though this will use OR 1=1), allowing the query to succeed.

For example,
if a normal query is something like:

SELECT <column_list>
FROM <table>
WHERE <filter condition>;

This will return a subset of the data from the table based on the filter condition.
With SQL injection, adding OR 1=1 to the end of the query would cause all records to be retuned.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:pma111
ID: 40311735
So all in all if I delete it from this query it will have no impact on the results.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 40311740
>>"So all in all if I delete it from this query it will have no impact on the results."

no

but I'm surprised no-one has mentioned moving the join condition, which simplifies the where clause i.e.

SELECT aip.*
FROM ap_invoice_lines_all aila
INNER JOIN ap_invoice_payments_all aip ON aila.invoice_id=aip.invoice_id
WHERE aila.po_line_id is null
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40311743
but I'm surprised no-one has mentioned moving the join condition, which simplifies the where clause i.e.
I suppose the reason for this is just the "Oracle way of writing SQL statements" ;-) Many people are used to this...
BUT, I agree with Paul: there are numerous reasons for using and migrating to ANSI join usage instead of native Oracle or MS SQL syntaxes. I try it myself not to use them and write proper ANSI SQLs!
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40313506
Technically, we can't actually know if removing the [1=1] will cause a problem or not without more info. Earlier comments have mentioned possible purposes for it. If we assume a fully static SQL statement, there should be no problem and no reason for it to be there. But until we know details of the application, of how the statement is created and used, we can't know if you're asking about a static or dynamic statement.

If it's a dynamic statement, you will want to review very carefully any code that modifies the statement before it's executed. The [1=1] might be used by that code even though the final statement doesn't care if it's included or not when it's executed.

Tom
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 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

20 Experts available now in Live!

Get 1:1 Help Now