PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL statement evaluation order

I’m learning PostgreSQL and want to understand the exact order in which SQL statements are evaluated. After researching, I found the following evaluation order:

 1. FROM 
 2. ON 
 3. JOIN 
 4. WHERE 
 5. GROUP BY 
 6. HAVING
 7. SELECT 
 8. AS  
 9. DISTINCT 
 10. ORDER BY 
 11. LIMIT / OFFSET

Based on this order, it seems that when I create an alias, I can’t use it in the clauses that are evaluated before the SELECT statement. However, I’m confused about how this works with subqueries. For example:

SELECT first_name, last_name
    FROM customer AS c
    WHERE EXISTS(
    (SELECT * FROM payment AS p
    WHERE p.customer_id = c.customer_id
    AND amount > 11)
    );

How can the subquery use the alias c and, how does the subquery get access to the alias c before it is created?

Could someone provide insights how does this work?

Thank you in advance for the help!

Tags:

2

Answers


  1. 0

    This is mentioned in the docs:

    4.2.11. Scalar Subqueries

    A scalar subquery is an ordinary SELECT query in parentheses that returns exactly one row with one column. …. The SELECT query is executed and the single returned value is used in the surrounding value expression. …. The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery. See also Section 9.23 for other expressions involving subqueries.

    Section 9.23.1 EXISTS also says the same thing.

    Note also:

    7.2.1.5. LATERAL Subqueries

    Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)

    This is not the same as a scalar subquery. A scalar subquery is used in place of a single value in an expression, and must only return a single row (or none). Whereas a lateral join can return multiple rows, and joins back to the previous table references as normal.

    As a side point, your list of evaluation order isn’t accurate. SELECT and AS are essentially the same thing. ON and JOIN are also. You are also missing Window Functions which come after HAVING, and set operators UNION and friends, which come after DISTINCT but before ORDER BY.

    Login or Signup to reply.
  2. 0

    c is an alias for a table, so it belongs to the FROM clause, so it makes sense that it is available in a subquery in WHERE.

    Consider the following query:

    SELECT t.c AS x
FROM (VALUES (1), (2)) AS t(c);

    x is a column alias, t and c are table aliases.

    This will work:

    SELECT t.c AS x
FROM (VALUES (1), (2)) AS t(c)
WHERE EXISTS (SELECT WHERE t.c = 1);

    But this will not:

    SELECT t.c AS x
FROM (VALUES (1), (2)) AS t(c)
WHERE EXISTS (SELECT WHERE x = 1);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search