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.
Best Answer
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:
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 iti
and this derived table has two columns,product_id
anditem_cnt
(anditem_cnt
is an alias for the expressionCOUNT(*)
. 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 theON
condition and thei.item_cnt
in theSELECT
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 theFROM
part.)For the query execution, the two tables (aliased
i
andp
) 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, theproducts
may not be a base table either. It can very well be a viewed table, usually referred as a view.)