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

sql database where-clause
MozenRath picture MozenRath · Feb 4, 2012 · Viewed 55.2k times · Source

I saw a query run in a log file on an application. and it contained a query like:

SELECT ID FROM CUST_ATTR49 WHERE 1=0

what is the use of such a query that is bound to return nothing?

Answer

Andrea Colleoni picture Andrea Colleoni · Feb 4, 2012

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
  • manage many OR conditions in dynamic queries (e.g WHERE 1=0 OR <condition>)

Related questions

Selecting rows where remainder (modulo) is 1 after division by 2?

There is a column in options that hold an integer. I want to select the row only if that value % 2 = 1. I know this can be done in 2 queries but is it possible to do it in 1?

Read More
Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More