SQL With Recursive All, Union ALL

https://www.techonthenet.com/mysql/union_all.php

UNION ALL

The MySQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query and it does not remove duplicate rows between the various SELECT statements.

Each SELECT statement within the MySQL UNION ALL operator must have the same number of fields in the result sets with similar data types.

 https://stackoverflow.com/questions/18840998/recursive-in-sql

The syntax that you are using looks like Postgres. "Recursion" in SQL is not really recursion, it is iteration. Your statement is:

WITH RECURSIVE t(n) AS (

    SELECT 1

    UNION ALL

    SELECT n+1 FROM t WHERE n < 100

)

SELECT sum(n) FROM t;

The statement for t is evaluated as:

Evaluate the non-self-referring part (select  1).

Then evaluate the self-referring part. (Initially this gives 2.)

Then evaluation the self-referring part again. (3).

And so on while the condition is still valid (n < 100).

When this is done the t subquery is finished, and the final statement can be evaluated.

The RECURSIVE from the query doesn't mean anything: it's just another name like n or t. What makes things recursive is that the CTE named t references itself inside the expression. To produce the result of the expression, the query engine must therefore recursively build the result, where each evaluation triggers the next. It reaches this point: SELECT n+1 FROM t... and has to stop and evaluate t. To do that, it has to call itself again, and so on, until the condition (n < 100) no longer holds. The SELECT 1 provides a starting point, and the WHERE n < 100 makes it so that the query does not recur forever.

SQL integer integer division, and integer decimal Division

 https://stackoverflow.com/questions/3443672/integer-division-in-sql-server

SELECT cast(151/6 AS DECIMAL(9,2))
SELECT 151/6

NOTE the above two commands will product integer result as 151 is integer and 6 is integer, 151/6 will produce integer 25

For SQL to get decimal result, at least one of the numeber has to be decimal 

SELECT 151/CAST(6 AS DECIMAL (9,2))

SQL left join(left outer join, difference of putting condition under join and under where)

SQL Server LEFT JOIN: conditions in ON vs. WHERE clause

The following query finds the products that belong to the order id 100:

1 2 3 4 5 6 7 8 9 10

SELECT     product_name,     order_id FROM     production.products p LEFT JOIN sales.order_items o    ON o.product_id = p.product_id WHERE order_id = 100 ORDER BY     order_id;

Let’s move the condition order_id = 100 to the ON clause:

1 2 3 4 5 6 7 8 9 10 11

SELECT     p.product_id,     product_name,     order_id FROM     production.products p     LEFT JOIN sales.order_items o          ON o.product_id = p.product_id AND             o.order_id = 100 ORDER BY     order_id DESC;

The query returned all products but only the order with id 100 has the associated product’s information.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

Same for mysql

https://www.sqlservertutorial.net/sql-server-basics/sql-server-left-join/

https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause

https://www.mysqltutorial.org/mysql-left-join.aspx