Tutorial :FROM clause necessary in every SELECT statement



Question:

is the FROM clause necessary in every SELECT statement?Thanks..


Solution:1

Not really - you can use SELECT to initialize a variable.

Examples from here:

mysql> SELECT 1 + 1; -> 2


Solution:2

Not in MySQL, no. You could do this:

SELECT 1 + 1;  

It will be in some DBMSs though - Oracle for example would require you to do:

SELECT 1 + 1 FROM DUAL;  


Solution:3

No. you can very easily do

SELECT 1+1  


Solution:4

(EDIT: Missed the the MYSQL tab.)

This depends on the database.

In Oracle, IIRC, the from is required. But Oracle has a table DUAL which always returns one row for cases where all the work is being done in the SELECT clause.

On the other hand, SQL Server does not require the FROM, to return the value of a variable just select it:

SELECT @myVar  


Solution:5

As mentioned by ocedecio FROM is not required in every statement.

For example I tend to use the following syntax to initialise a table with data

insert into status(code, description)  select 'O', 'Open'  union all select 'C', 'Closed'  union all select 'P', 'Parked'  

etc..

EDIT (following comments by gamecat)

The example above demonstrates when SELECT without FROM may have a practical use. You can of course just call:

select 'O', 'Open'  union all select 'C', 'Closed'  union all select 'P', 'Parked'  


Solution:6

Basically in SQL server 2005 FROM keyword is not required for Scalar value functions and system functions but it mandatory for table valued functions.

Scalar valued functions can executable like below syntax

Select functionname(arguments)

Table valued functions can executable like below syntax

Select * from functionname(arguments)

else

Select col1,col2 from functionname(argument1,argument2)


Solution:7

Actually, both "SELECT 1" and "SELECT 1 FROM DUAL" work in latest versions of MySQL.

Interesting enough, "SELECT 1 WHERE 1 = 1" requires a FROM clause: "SELECT 1 FROM DUAL WHERE 1 = 1" is fine. In other platforms, like PostgreSQL, "SELECT 1 WHERE 1 = 1" works fine.

Another oddity: "SELECT 1 FROM DUAL GROUP BY 1 HAVING 1=1" works in MySQL 5.7, but fails in MySQL 5.5.


Summary of the required/optional FROM clause in different platforms:

  • MySQL, MariaDB, Amazon Aurora: optional or use "FROM dual".
  • Oracle: use "FROM dual".
  • IBM DB2: use "FROM sysibm.sysdummy1".
  • Informix: use "FROM systables WHERE tabid=1"
  • Firebird, InterBase: use "FROM rdb$database".
  • Microsoft Access: required, but no dummy table. Try using "FROM (SELECT COUNT(*) FROM first_table_found WHERE 1=0) AS dual".
  • SQL Server, SQL Azure, SQL Server CE, Sybase ASE, SQL Anywhere, Ingres, PostgreSQL, Redshift, SQLite: optional.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »