where 1=1

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
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
jtriftsMI and AutomationCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pma111Author Commented:
So all in all if I delete it from this query it will have no impact on the results.
0
PortletPaulfreelancerCommented:
>>"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
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
tliottaCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.