Solved

where 1=1

Posted on 2014-09-09
7
362 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

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

Title # Comments Views Activity
Can i Import Access Table Into Oracle Using Toad 36 173
Queries 15 38
Oracle - SQL Parse String 5 34
Include a logo in email body using Oracle utl_mail 11 30
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

770 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