SELECT from nothing

Question

Is it possible to have a statement like  SELECT  Hello world  WHERE 1   1   in SQL   The main thing I want to know  is can I SELECT from nothing  ie not have a FROM clause

User · Answer

For ClickHouse  the nothing is system one  SELECT 1 FROM system one

User · Answer

In Oracle   SELECT  Hello world  FROM dual   Dual equivalent in SQL Server   SELECT  Hello world

User · Answer

There is another possibility - standalone VALUES     VALUES   Hello World      Output   column1 Hello World     It is useful when you need to specify multiple values in compact way   VALUES  1   a     2   b     3   c      Output   column1     column2       1     a       2     b       3     c   DBFiddle Demo  This syntax is supported by SQLite PostgreSQL DB LUW MariaDB 10 3

User · Answer

Here is the most complete list of database support of dual from https   blog jooq org tag dual-table       In many other RDBMS  there is no need for dummy tables  as you can   issue statements like these   SELECT 1  SELECT 1   1  SELECT SQRT 2         These are the RDBMS  where the above is generally possible          H2   MySQL   Ingres   Postgres   SQLite   SQL Server   Sybase ASE         In other RDBMS  dummy tables are required  like in Oracle  Hence    you   ll need to write things like these   SELECT 1       FROM DUAL  SELECT 1   1   FROM DUAL  SELECT SQRT 2  FROM DUAL        These are the RDBMS and their respective dummy tables          DB2  SYSIBM DUAL   Derby  SYSIBM SYSDUMMY1   H2  Optionally supports DUAL   HSQLDB  INFORMATION SCHEMA SYSTEM USERS   MySQL  Optionally supports DUAL   Oracle  DUAL   Sybase SQL Anywhere  SYS DUMMY         Ingres has no DUAL  but would actually need it as in Ingres you cannot   have a WHERE  GROUP BY or HAVING clause without a FROM clause

User · Answer

In Firebird  you can do this   select  Hello world  from RDB DATABASE    RDB DATABASE is a special table that always has one row

User · Answer

I know this is an old question but the best workaround for your question is using a dummy subquery   SELECT  Hello World  FROM  SELECT name  Nothing   n WHERE 1 1   This way you can have WHERE and any clause  like Joins or Apply  etc   after the select statement since the dummy subquery forces the use of the FROM clause without changing the result

User · Answer

It s not consistent across vendors - Oracle  MySQL  and DB2 support dual   SELECT  Hello world    FROM DUAL      while SQL Server  PostgreSQL  and SQLite don t require the FROM DUAL   SELECT  Hello world    MySQL does support both ways

User · Answer

Try this   Single   SELECT    FROM  VALUES   Hello world    t1  col1  WHERE 1   1   Multi   SELECT    FROM  VALUES   Hello world     Hello world     Hello world    t1  col1  WHERE 1   1   more detail here   http   modern-sql com use-case select-without-from

User · Answer

You can   I m using the following lines in a StackExchange Data Explorer query   SELECT  SELECT COUNT    FROM VotesOnPosts WHERE VoteTypeName    UpMod  AND UserId    UserID AND PostTypeId   2  AS TotalUpVotes   SELECT COUNT    FROM Answers WHERE UserId    UserID  AS TotalAnswers   The Data Exchange uses Transact-SQL  the SQL Server proprietary extensions to SQL    You can try it yourself by running a query like   SELECT  Hello world

User · Answer

In SQL Server type   Select  Your Text    There is no need for the FROM or WHERE clause

User · Answer

I think it is not possible  Theoretically  select performs two sorts of things    narrow broaden the set  set-theory   mapping the result    The first one can be seen as a horizontal diminishing opposed to the where-clause which can be seen as a vertical diminishing  On the other hand  a join can augment the set horizontally where a union can augment the set vertically                   augmentation          diminishing horizontal     join select              select    vertical          union            where inner-join   The second one is a mapping  A mapping  is more a converter  In SQL it takes some fields and returns zero or more fields  In the select  you can use some aggregate functions like  sum  avg etc  Or take all the columnvalues an convert them to string  In C  linq  we say that a select accepts an object of type T and returns an object of type U    I think the confusion comes by the fact that you can do  select  howdy  from  lt table name gt   This feature is the mapping  the converter part of the select  You are not printing something  but converting  In your example    SELECT   WHERE 1   1   you are converting nothing null into  Hello world  and you narrow the set of nothing   no table into one row  which  imho make no sense at all   You may notice that  if you don t constrain the number of columns   Hello world  is printed for each available row in the table  I hope  you understand why by now  Your select takes nothing from the available columns and creates one column with the text   Hello world    So  my answer is NO  You can t just leave out the from-clause because the select always needs table-columns to perform on

User · Answer

For DB2    VALUES  Hello world      You can do multiple  rows  as well    VALUES  Hello world     Goodbye world       You can even use them in joins as long as the types match   VALUES 1  Hello world   UNION ALL VALUES 2  Goodbye world

User · Answer

In Standard SQL  no  A WHERE clause implies a table expression   From the SQL-92 spec      7 6   where clause       Function      Specify a table derived by the   application of a  search condition  to   the result of the preceding  from   clause     In turn      7 4  from clause       Function      Specify a table derived from one or more named tables    A Standard way of doing it  i e  should work on any SQL product    SELECT DISTINCT  Hello world  AS new value   FROM AnyTableWithOneOrMoreRows  WHERE 1   1       assuming you want to change the WHERE clause to something more meaningful  otherwise it can be omitted

User · Answer

I m using firebird First of all  create a one column table named  NoTable  like this  CREATE TABLE NOTABLE      NOCOLUMN              INTEGER    INSERT INTO NOTABLE VALUES  0   -- You can put any value   now you can write this  select  hello world  as name   from notable  you can add any column you want to be shown

[sql] SELECT from nothing?

Examples related to sql