About subqueries in MySQL and PostgreSQL

Question

When I use MySQL, the following query is clean.

SELECT 1 as num1, (select(num1 + 1)) as num2 

But PostgreSQL returns an error.

 ERROR: column "num1" does not exist 

Why does he react differently?

Answer 1

you should use PostgreSQL syntax because PgSql does not support a similar query so try

 WITH tblcontent AS (SELECT 1 as num1) SELECT num1, num1 + 1 AS num2 from tblcontent 

Answer 2

The question is not why Postgres does not support this syntax, but why MySQL does. In most RDBMSs, aliases are only allowed after the query has completed, so you cannot use them from a query that declares them. For example, the more common usecase function is that you cannot use a column alias in the where clause of the query / table-element element that declared it.

One way to use subqueries is:

 SELECT num1, num1 + 1 AS num2 FROM (SELECT 1 AS num1) t 

Answer 3

I cannot find a good source, but I am sure that the standard does not require the presence of aliases of the projected columns inside the correlated subquery. I just tried this on SQL Server and Oracle, and both of them seem to agree with PostgreSQL here, abandoning the use of x in the correlated subquery, regardless of the position of the external selection it is in.

This is similar to how MySQL allows the following query:

 SELECT a + 1 x FROM t ORDER BY x + 1 

but SQL Server and Postgres do not. MySQL seems more permissive regarding the use of aliases in various statements than the standard requires.