CHOOSE FROM Nothing?

Question

Is it possible to have an operator like

SELECT "Hello world" WHERE 1 = 1 

in SQL?

The main thing I want to know is that I can choose from nothing, i.e. not have a FROM clause.

Answer 1

This is not agreed between the providers - support for Oracle, MySQL and DB2 is twofold:

 SELECT 'Hello world' FROM DUAL 

... while SQL Server, PostgreSQL and SQLite do not require FROM DUAL :

 SELECT 'Hello world' 

MySQL supports both ways.

Answer 2

In Oracle :

 SELECT 'Hello world' FROM dual 

Double equivalent in SQL Server :

 SELECT 'Hello world' 

Answer 3

Try it.

Single:

 SELECT * FROM (VALUES ('Hello world')) t1 (col1) WHERE 1 = 1 

Dot Matrix:

 SELECT * FROM (VALUES ('Hello world'),('Hello world'),('Hello world')) t1 (col1) WHERE 1 = 1 

more details here: http://modern-sql.com/use-case/select-without-from

Answer 4

In the SQL Server type:

 Select 'Your Text' 

No need for FROM or WHERE .

Answer 5

You can. I use the following lines in the 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 

Data exchange uses Transact-SQL (proprietary SQL Server extensions for SQL).

You can try this yourself by running a query like:

 SELECT 'Hello world' 

Answer 6

Here is the most comprehensive list of dual database support from https://blog.jooq.org/tag/dual-table/ :

Many other DBMSs do not need dummy tables, since you can issue statements like this:

 SELECT 1; SELECT 1 + 1; SELECT SQRT(2); 

This is a DBMS, where, as a rule, it is possible:

• H2
• MySQL
• Ingres
• Postgres
• Sqlite
• SQL Server
• Sybase ASE

Other DBMSs require dummy tables, for example, in Oracle. Therefore, you need to write things like this:

 SELECT 1 FROM DUAL; SELECT 1 + 1 FROM DUAL; SELECT SQRT(2) FROM DUAL; 

These are DBMS and their corresponding dummy tables:

• DB2: SYSIBM.DUAL
• Derby: SYSIBM.SYSDUMMY1
• H2: Optional DUAL support
• HSQLDB: INFORMATION_SCHEMA.SYSTEM_USERS
• MySQL: optionally supports DUAL
• Oracle: DUAL
• Sybase SQL Anywhere: SYS.DUMMY

Ingres does not have a DUAL, but in fact it is necessary, as in Ingres, you cannot have a WHERE, GROUP BY or HAVING clause without a FROM clause.

Answer 7

In standard SQL, no. The WHERE implies a table expression.

From the SQL-92 specification:

7.6 "where clause"

Function

Indicate the table obtained by applying the "search terms" to the result of the previous "from clause".

In its turn:

7.4 "from offer"

Function

Specify a table obtained from one or more of the named tables.

The standard way to do this (i.e. work with any SQL product):

 SELECT DISTINCT 'Hello world' AS new_value FROM AnyTableWithOneOrMoreRows WHERE 1 = 1; 

... if you want to change the WHERE to something more meaningful, otherwise you can omit it.

Answer 8

I think that this is impossible. Theoretically: select does two kinds of things:

• narrow / expand the set (set theory);

• display result.

The first can be seen as a horizontal decrease, opposite to the where-where clause, which can be seen as a vertical decrease. On the other hand, a join can increase horizontal dialing when a join can increase vertical dialing.

  augmentation diminishing horizontal join/select select vertical union where/inner-join 

The second is mapping. Display is more of a converter. In SQL, it takes multiple fields and returns zero or more fields. In the select element, you can use some aggregate functions, such as sum, avg, etc. Or take all the column values ​​and convert them to a string. In C # linq, we say that select takes an object of type T and returns an object of type U.

I think the confusion is due to what you can do: select 'howdy' from <table_name> . This function is a display, part of the selection converter. You do not print anything, but convert! In your example:

 SELECT " WHERE 1 = 1 

you do not convert anything / null to "Hello world" and narrow the set of nothing / no tables to one row, which imho makes no sense.

You may notice that if you do not limit the number of columns, "Hello world" is printed for each available row in the table. I hope you understand why by now. Your selection does not accept any of the available columns and creates a single column with the text: "Hello world" .

So my answer is NO. You cannot simply refuse the from clause because column columns are always required for selection.

Answer 9

There is nothing system.one for ClickHouse

 SELECT 1 FROM system.one 

Answer 10

There is another possibility - standalone VALUES() :

 VALUES ('Hello World'); 

Exit:

 column1 Hello World 

---

This is useful when you need to specify multiple values ​​in a compact form:

 VALUES (1, 'a'), (2, 'b'), (3, 'c'); 

Exit:

 column1 column2 1 a 2 b 3 c 

DBFiddle Demo

This syntax is supported by SQLite / PostgreSQL / DB LUW / MariaDB 10.3.

Answer 11

I know this is an old question, but the best solution for your question is to use a subarray:

 SELECT 'Hello World' FROM (SELECT name='Nothing') n WHERE 1=1 

This way you can have WHERE and any clause (for example, “Connections” or “Apply”, etc.) after the select statement, since the dummy subquery forces the FROM clause without changing the result.

Answer 12

I am using firebird. First of all, create 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 famous

you can add any column you want to show

Answer 13

In Firebird you can do this:

 select "Hello world" from RDB$DATABASE; 

RDB $ DATABASE is a special table that always has one row.

Answer 14

For DB2:

 'VALUES('Hello world')' 

You can also make several "lines":

 'VALUES('Hello world'),('Goodbye world');' 

You can even use them in joins if the types match:

 VALUES(1,'Hello world') UNION ALL VALUES(2,'Goodbye world');