NULL AS OUT in SELECT statements-CodePudding

I am trying to solve this exercise:

Under the assumption that receipts of money (inc) and payouts (out) can be registered any number of times a day for each collection point [i.e. the code column is the primary key], display a table with one corresponding row for each operating date of each collection point. Result set: point, date, total payout per day (out), total money intake per day (inc). Missing values are considered to be NULL.

After several hours of headbanging I found this solution online:

SELECT X.POINT,X.DATE,SUM(OUT),SUM(INC) FROM (
    SELECT I.POINT,I.DATE,NULL AS OUT, SUM(I.INC) AS INC FROM INCOME I
    GROUP BY I.POINT,I.DATE
    UNION 
    SELECT O.POINT,O.DATE,SUM(O.OUT) AS OUT , NULL AS INC FROM OUTCOME O
    GROUP BY O.POINT,O.DATE) AS X
GROUP BY POINT,DATE

I tried to understand how this works. I googled all variations of "NULL AS OUT" but I could not find any explanation/concept. All results point out to stored procedures which is not what I am looking fro I think.

Can someone explain to me how these lines with "AS OUT" work, please?

SELECT I.POINT,I.DATE,NULL AS OUT, SUM(I.INC) AS INC FROM INCOME I
GROUP BY I.POINT,I.DATE
UNION 
SELECT O.POINT,O.DATE,SUM(O.OUT) AS OUT , NULL AS INC FROM OUTCOME O

On the left - The complete version of both tables On the right the result

CodePudding user response：

out is probably an unfortunate name there, but other than that, there's nothing magical there.
"null" is the literal value of null.
"as out" assigns a column alias to the selected null.

Syntactically, this is the equivalent to any other literal value with any other alias, e.g., SELECT 'some_varchar_literal' AS some_alias or SELECT 123 AS numeric_alias.

CodePudding user response：

Copying comment as answer to mark it as worked

is this SQL server or mysql. from my understanding I think as out means null is referred to a column named OUT, basically alias name for a column. Income table will not have outcome and outcome table will not have income. Hence in both the select statement, the respective values are marked as null (NULL AS OUT & NULL AS INC). when you perform aggregate on these columns then the null values will be ignored.

you can use NULL as when your come across a similar union statement where one of the two tables does not have a column. In that case you can create this missing column as a dummy one and use it in your code.