Menu & Search
Why Use WHERE 1=2 in SQL

Why Use WHERE 1=2 in SQL

Some time ago I wrote short blog post on why use WHERE 1=1 in SQL. This time it’s why use WHERE 1=2, but really this can be WHERE 1=9 or anything that isn’t a 1, we just want the false statement.

WHERE 1=1 is TRUE; has no difference on execution times and can be used for ease of adding/removing comments & commas on queries that you are building.

WHERE 1=2 is FLASE; is usually used for building the structure of a table by copying it from another, without copying any of the rows. It does not copy keys & constraints.

The following example SQL should help explain this, and should be able to run on any of the tagged systems – although please do amend the table names and add a schema name if necessary (depends on SQL database software).

-- Create table copy from another table
CREATE TABLE infoschema_tables AS (SELECT * FROM REMOVEinformation_schema.tables WHERE 1=1);

-- Count rows of created table (not required in this psql terminal example as it shows row counts)
SELECT COUNT(*) FROM infoschema_tables;

-- Drop table
DROP TABLE infoschema_tables;

-- Create table copy from another table but do not copy rows
CREATE TABLE infoschema_tables AS (SELECT * FROM REMOVEinformation_schema.tables WHERE 1=2);

-- Count rows of created table
SELECT COUNT(*) FROM infoschema_tables;

-- Clean-up drop table
DROP TABLE infoschema_tables;

Share

0 Comments