Why I cannot use a table alias inside a subquery?-CodePudding

I am trying to run the following query:

SELECT customers.id, customers.name, o.order_date
FROM customers, 
    (SELECT id, 
     order_date 
     FROM orders ORDER BY order_date DESC) as o
WHERE o.id = (
    SELECT id
    FROM o
    WHERE customer_id = customers.id
    LIMIT 1)
ORDER BY customer_id;

However it results with an error relation "o" does not exist

I understand that there is an issue with an alias of a subquery above it but I don't know how can I fix it. The reason why I'm not referencing "orders" table directly in WHERE clause is because I want to optimise it by creating a subquery with just 2 columns which are ordered, so this query is not executed on entire table each time a comparison in WHERE clause is made (is it how it works?).

Is there any other way I can write this query?

CodePudding user response：

That's not the only problem with your query - you're also referencing customer_id that you did not select from orders. Once you fix that and move your subquery to a CTE, it'll work:

with o as (
  SELECT 
    id, 
    customer_id,
    order_date 
  FROM orders 
  ORDER BY order_date DESC)
SELECT 
  customers.id, 
  customers.name, 
  o.order_date
FROM customers, 
     o
WHERE o.id = (
    SELECT id
    FROM o
    WHERE o.customer_id = customers.id
    LIMIT 1)
ORDER BY customer_id;

Demo

EDIT:

When I am joining these tables I want to join only first match from orders table based on date (the most recent one)

MAX() would be sufficient to get customers and their most recent order dates.

SELECT 
  c.id, 
  c.name, 
  max(o.order_date) as most_recent_order_date
FROM customers c 
  left join orders o 
  on o.customer_id=c.id
group by c.id, c.name
ORDER BY c.id;