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
Solved

where 1=1

Posted on 2014-09-09
7
371 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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…
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

792 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