Why use "where 1=2" on a SQL CREATE TABLE syntax?

Sql

Sql Problem Overview

CREATE TABLE EMPL_DEMO AS SELECT * FROM employees WHERE 1=2;

I read this statement somewhere on the internet but I couldn't understand the WHERE 1=2.

Will anyone please, explain this?

Sql Solutions

Solution 1 - Sql

This type of command is usually used to copy the structure of one table to another. In this case, EMPL_DEMO will have the same column structure of employees, except for the keys or constraints.

The 1=2 always evaluates to False which prevents you from copying any of the rows.

Solution 2 - Sql

CREATE TABLE (Create A New Table)

EMPL_DEMO (Called EMPL_DEMO)

AS (With The Data and structure of)

SELECT * FROM employees WHERE 1=2; (Everything in employees where 1=2. Since 1 is never 2 - copy the structure and all 0 matching rows)

..Essentially copy structure and not data.

Solution 3 - Sql

This syntax does the same, but it's more obvious, it creates a table with the same structure, with no data.

CREATE TABLE EMPL_DEMO AS SELECT * FROM employees limit 0;

Solution 4 - Sql

This can be useful to copy structure of a table excluding its constraints, keys, indexes, identity property and data rows.

This query will create EMPL_DEMO table with no rows copied from employees table as WHERE 1=2 condition is always going to be evaluated as FALSE.

  CREATE TABLE EMPL_DEMO 
  AS 
  SELECT * 
  FROM employees 
  WHERE 1=2;

Solution 5 - Sql

Trust in Google, my friend. From the bottom of the first result when you google that statement:

> For example: > > CREATE TABLE suppliers > AS (SELECT * > FROM companies WHERE 1=2); > > This would create a new table called suppliers that included all > columns from the companies table, but no data from the companies > table.

Solution 6 - Sql

In SQL Server

select * into table1 from table2 where 1=2(Only Structure)

select * into table1 from table2 where 1=1(Structure with data)

Solution 7 - Sql

Oracle:

CREATE TABLE EMPL_DEMO AS SELECT * FROM employees WHERE 1=2; //just structure not data

CREATE TABLE EMPL_DEMO AS SELECT * FROM employees WHERE 1=1; //ststructure and data

Better understanding in the Teradata Database:

CREATE TABLE EMPL_DEMO AS Memployees with no data; //structure

CREATE TABLE EMPL_DEMO AS Memployees with data; //structure and data
Categories
Recommended Sql Solutions

Previous Solution:

Need holistic explanation about Rust's cell and reference counted types

Rust

Next Solution:

Inconsistent behaviour with tm_map transformation functions when using multiple cores

RParallel ProcessingText MiningTmDoparallel

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionOmid ShagiwalView Question on Stackoverflow
Solution 1 - SqlDrZooView Answer on Stackoverflow
Solution 2 - SqlFazer87View Answer on Stackoverflow
Solution 3 - SqlfraffView Answer on Stackoverflow
Solution 4 - SqlVishwanath DalviView Answer on Stackoverflow
Solution 5 - SqlSilas BView Answer on Stackoverflow
Solution 6 - SqlSai Kumar ReddyView Answer on Stackoverflow
Solution 7 - Sql13957813157View Answer on Stackoverflow