Why Use WHERE 1=2 in SQL

In a previous blog post, I explained why WHERE 1=1 is often used in SQL queries. This time, let’s discuss WHERE 1=2 — or any condition that results in a FALSE value, such as WHERE 1=9. Essentially, the point is to use a condition that is guaranteed to evaluate to FALSE.

WHERE 1=1 vs. WHERE 1=2

WHERE 1=1: This condition is always TRUE and has no impact on the execution time of the query. It’s often used to make query-building easier, particularly when dynamically adding or removing conditions, comments, or commas.

WHERE 1=2: This condition is always FALSE. It’s commonly used when you want to create the structure of a table by copying its schema but without copying the rows. Additionally, it ensures that keys and constraints aren’t copied over.

Practical Example

The following example SQL should help explain this, and the SQL Syntax used below can be run on MySQL, MSSQL, Redshift, Postgres & more.

The syntax will work on other SQL systems, however, the table names used in the SQL below will not be available on all platforms. Please amend SQL table names as necessary.

-- Create a table structure by copying the schema, but not the rows
CREATE TABLE infoschema_tables AS 
    (SELECT * FROM REMOVEinformation_schema.tables WHERE 1=1);

-- Count the rows in the newly created table
-- (This step may not be necessary in your specific terminal, as it will show row counts automatically)
SELECT COUNT(*) FROM infoschema_tables;

-- Clean up: Drop the table
DROP TABLE infoschema_tables;

-- Create a table structure by copying the schema without copying the rows
CREATE TABLE infoschema_tables AS 
    (SELECT * FROM REMOVEinformation_schema.tables WHERE 1=2);

-- Count the rows in the newly created table (should return 0)
SELECT COUNT(*) FROM infoschema_tables;

-- Clean up: Drop the table
DROP TABLE infoschema_tables;

In conclusion, WHERE 1=2 is a useful technique for creating an empty table with the same structure as another, without copying any data, making it ideal for schema migrations or creating backup structures, and this syntax is compatible across various SQL platforms, though table names may vary.