what is the meaning of 1=1, 1=2 or 6=8 when creating table in SQL-CodePudding

when creating a copy of another table, lets say table 'tab1' already exists and I want to make a copy of it named 'tab2'

CREATE TABLE tab2 AS SELECT * FROM tab1 WHERE 1=1;

creates a copy of structure and data.

CREATE TABLE tab2 AS SELECT * FROM tab1 WHERE 1=2;

creates only the structure. to my knowledge 1=1 means TRUE and 1=2 means FALSE.

instead of 1=1 can I say 69=69 ? instead of 1=2 can I say 99=2 ?

CodePudding user response：

where 1=1 simply means "where true" and it WILL copy all the data from the old table into the new table.

where 1=2 means "where untrue" and because no rows will match that condition NO DATA is copied to the new table (but the new table is a copy of the old table's structure).

So, where 1=1 is used to get the both the table structure and data, while where 1=2 is used to copy a table structure but with no data in the new table.

Yes, you could use 69=69 (true) or 99=2 (untrue) but it is more conventional not to be inventive here as it could get very confusing for those who come later to support your code.