What column is this alias referring to?

Question

I've read the following code a few times trying to figure out what it is doing.

"A right outer join is that which returns the unmatched rows from the right tables with matched rows from the left table."

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? I'm preparing for 1z0-047 so any help would be appreciated.

---

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;

---

I thought that the column should exist. But here is the Oracle docs page with all example schemas. If I search with ctrl+f for item_cnt, I find nothing: Oracle example schemas.

Answer 1

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias?

The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.)

But this part of the code:

FROM 
     (SELECT product_id, COUNT (*) item_cnt
      FROM order_items
      GROUP BY product_id) i

provides a derived table. The column exists during the time that the query runs, in the derived table i. The (SELECT product_id, ... item_cnt ...) AS i defines a derived table, it names it i and this derived table has two columns, product_id and item_cnt (and item_cnt is an alias for the expression COUNT(*). There is no count(*) column either in any of the tables!)

So, later in the code, you can use them. The i.product_id is used in the ON condition and the i.item_cnt in the SELECT list.

(And it's a bit confusing that it seems to appear earlier and not later. That's because the SELECT part of a query is logically executed after the FROM part.)

FROM (...) i RIGHT OUTER JOIN products p ON ... 

For the query execution, the two tables (aliased i and p) have no difference. It doesn't matter that one is a base table and the other a derived table. They can be used the same way. (and as far as we know, the products may not be a base table either. It can very well be a viewed table, usually referred as a view.)