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;

Related Posts
0 Comments