About subquery in MySQL and PostgreSQL

Question

When I use MySQL, the following query runs clean.

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

But PostgreSQL is returning an error.

ERROR: column "num1" does not exist

Why is it responding differently?

Answer 1

The question shouldn't be why does Postgres not support this syntax, but why MySQL does. In most RDBMSs, aliases are only resolved after the query is executed, so you can't use them from within the query that declares them. E.g., the more common usecase - you cannot use a column alias in a where clause of the query/table-element that declared it.

One way around this is to use subqueries:

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

Answer 2

you should use PostgreSQL syntax , because PgSql does not support sub query this way, try this

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

Answer 3

I can't find any good source, but I am quite sure that the standard does not require aliases of projected columns to be visible inside a correlated sub query. I just tried it in SQL Server and Oracle, and both of them seem to agree with PostgreSQL here, rejecting using x in a correlated sub query, no matter in which clause of the outer select it is located.

It 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 to be more permissive with regard to using aliases in various clauses than the standard requires.