Solved

where 1=1

Posted on 2014-09-09
7
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 143

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

696 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