In Oracle, Is Starting The SQL Query's Where Clause with 1=1 Useful

In Oracle, is starting the SQL Query's WHERE clause with 1=1 useful?

It's done to simplify dynamic SQL generation. Basically each condition can be added as AND <condition> without treating the first condition as special (it's preceded by WHERE not AND) or even worrying if there should be a WHERE clause at all.

So just write it off as easy of use or, arguably, laziness.

What is the purpose of using WHERE 1=1 in SQL statements?

Yeah, it's typically because it starts out as 'where 1 = 0', to force the statement to fail.

It's a more naive way of wrapping it up in a transaction and not committing it at the end, to test your query. (This is the preferred method).

Why would someone use WHERE 1=1 AND conditions in a SQL clause?

If the list of conditions is not known at compile time and is instead built at run time, you don't have to worry about whether you have one or more than one condition. You can generate them all like:

and <condition>

and concatenate them all together. With the 1=1 at the start, the initial and has something to associate with.

I've never seen this used for any kind of injection protection, as you say it doesn't seem like it would help much. I have seen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.

T-SQL 1=1 Performance Hit

It is likely that if you use the profiler and look, you will end up seeing that the optimizer will end up ignoring that more often than not, so in the grand scheme of things, there probably won't be much in the way of performance gain or losses.

Which performs first WHERE clause or JOIN clause

The conceptual order of query processing is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

But this is just a conceptual order. In fact the engine may decide to rearrange clauses. Here is proof. Let's make 2 tables with 1000000 rows each:

CREATE TABLE test1 (id INT IDENTITY(1, 1), name VARCHAR(10))
CREATE TABLE test2 (id INT IDENTITY(1, 1), name VARCHAR(10))

;WITH cte AS(SELECT -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) d FROM
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t3(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t4(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t5(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t6(n))

INSERT INTO test1(name) SELECT 'a' FROM cte

Now run 2 queries:

SELECT * FROM dbo.test1 t1
JOIN dbo.test2 t2 ON t2.id = t1.id AND t2.id = 100
WHERE t1.id > 1

SELECT * FROM dbo.test1 t1
JOIN dbo.test2 t2 ON t2.id = t1.id
WHERE t1.id = 1

Notice that the first query will filter most rows out in the join condition, but the second query filters in the where condition. Look at the produced plans:

1 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(100)

2 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(1)

This means that in the first query optimized, the engine decided first to evaluate the join condition to filter out rows. In the second query, it evaluated the where clause first.

What is the purpose of Order By 1 in SQL select statement?

This:

ORDER BY 1

...is known as an "Ordinal" - the number stands for the column based on the number of columns defined in the SELECT clause. In the query you provided, it means:

ORDER BY A.PAYMENT_DATE

It's not a recommended practice, because:

1. It's not obvious/explicit
2. If the column order changes, the query is still valid so you risk ordering by something you didn't intend

why would you use WHERE 1=0 statement in SQL?

A query like this can be used to ping the database. The clause:

WHERE 1=0

Ensures that non data is sent back, so no CPU charge, no Network traffic or other resource consumption.

A query like that can test for:

• server availability
• CUST_ATTR49 table existence
• ID column existence
• Keeping a connection alive
• Cause a trigger to fire without changing any rows (with the where clause, but not in a select query)
• manage many OR conditions in dynamic queries (e.g WHERE 1=0 OR <condition>)

Conditional where clause in Oracle SQL query

You can do this:

select * from HR_DATA
where (:P2_SELECT_DATE is null
      or (:P2_SELECT_DATE between person_eff_start_date and person_eff_end_date
         and :P2_SELECT_DATE between nvl(assign_eff_start_date,sysdate-1) 
                                 and nvl(assign_eff_end_date, sysdate+1)
      )

Sometimes if there are a lot of optional filters it can be more efficient to build the query dynamically:

declare
    q long;
begin    
    q := 'select * from HR_DATA where 1=1';

    if :P2_SELECT_DATE is null then
       q := q || ' and :P2_SELECT_DATE between person_eff_start_date and person_eff_end_date'
              || ' and :P2_SELECT_DATE between nvl(assign_eff_start_date,sysdate-1)'
              || ' and nvl(assign_eff_end_date, sysdate+1);
    end if;
    return q;
end;

The "where 1=1" is just a trick so that any conditionally appended filters alsways start with " and".

---

Related Topics