[Note] SQL Difference Analysis in popular databases: "Do not retrieve data from entity tables"

Do not retrieve data from the entity table

Sometimes we need to query data that cannot be obtained from any entity table, such as using number 1 as the result set or calculating the length of the string "ABC.

Solution 1:

Select 1 from t_employee // but there will be multiple 1 records, which are recorded according to the data table.

 

Select distinct 1 from t_employee // only one entry can appear, but it is not perfect.

 

MySQL and MSSQLServer allow the use of select statements without the from clause to query data that does not belong to any entity table:

Select 1

 

You can also use a function in a select statement without the from clause. For example, the following SQL statement uses the length of the string "ABC" as the result set:

Select length ('abc') // mysql. The result is 3.

Select Len ('abc') // MSSQLServer

 

You can also calculate multiple expressions in a select statement. For example, the following SQL statement uses 1, 2, 3, 'A', 'B', and 'C' as the result set:

Select 1, 2, 3, 'A', 'B', 'C'

 

This SELECT statement without the from clause is not allowed in Oracle, but we can use the Oracle system table as the table name in the from clause, A system table is a special built-in Oracle table. The most common system table is dual. For example, the following SQL statement uses the length of 1 and the string 'abc' as the result set:

Select 1, length ('abc') from dual

 

DB2 also does not support select statements without the from clause. It also uses a system table similar to Oracle. The most common system table is sysibm. sysdummy1. For example, the following SQL statement uses the length of 1 and the string 'abc' as the result.

Set:

Select 1, length ('abc') from sysibm. sysdummy1