What does "TOTAL ON" when used on a joined SELECT statement with parentheses actually do?-CodePudding

I'm currently learning SQL on Oracle and one of the examples that are given in my module is rather baffling, mainly because no explanation of it is actually given. I'm sure it's fairly basic but it uses the keywords "TOTAL ON" as part of it and I don't really know what this means or what is actually happening with this join.

I tried searching for a solution but understandably, using the word "TOTAL" brings up stuff on the SUM aggregate function.

Here is the statement:

SELECT CUSTOMER.*, TotalPurchased
FROM CUSTOMER
JOIN
    (
        SELECT CustomerID, SUM(PurchasedAmount) AS TotalPurchased
        FROM PURCHASE
        GROUP BY CustomerID;
    ) TOTAL ON TOTAL.CustomerID = CUSTOMER.CustomerID;

Can anyone please just tell me what this is? Thank you in advance.

CodePudding user response：

TOTAL is an alias for the sub-query, ON is the start of the clause that specifies the conditions by which the tables are joined.

You could rewrite your query as:

SELECT table_alias.*,
       subquery_alias.TotalPurchased
FROM   CUSTOMER table_alias
       JOIN (
         SELECT CustomerID, SUM(PurchasedAmount) AS TotalPurchased
         FROM PURCHASE
         GROUP BY CustomerID -- No ; here.
      ) subquery_alias
      ON subquery_alias.CustomerID = table_alias.CustomerID;

I guess the alias then is needed in order to have a name by which to join that subquery?

The alias helps to clarify which column belongs to which table but, in Oracle, it is not required if the columns are unique.

Note: Some RDBMS require that every sub-query has an alias; Oracle, however, does not.

For example:

SELECT dual.*,
       id
FROM   DUAL
       JOIN ( SELECT DUMMY AS id FROM DUAL )
       ON id = dummy;

Outputs:

DUMMY ID

X X

DUMMY	ID
X	X

As it is unambiguous which table contains which column.

But using:

SELECT *
FROM   DUAL
       JOIN DUAL
       ON dummy = dummy;

SELECT *
FROM   DUAL
       JOIN (SELECT DUMMY FROM DUAL)
       ON dummy = dummy;

Both raise the exception:

ORA-00918: column ambiguously defined

As the ON clause cannot distinguish which table/sub-query the dummy column refers to as the same identifier is used in both.

In either case you can fix the exception by giving the sub-query an alias (and maybe the table an alias) and then prefixing the column identifier with the table-name or the alias to clarify which is which.

db<>fiddle here

CodePudding user response：

TOTAL is just an alias for the nested table within the parenthesis. Maybe it would make more sense reformatted a bit:

SELECT CUSTOMER.*, TotalPurchased
FROM CUSTOMER
JOIN (
    SELECT CustomerID, SUM(PurchasedAmount) AS TotalPurchased
    FROM PURCHASE
    GROUP BY CustomerID
) AS TOTAL 
  ON TOTAL.CustomerID = CUSTOMER.CustomerID