Queries - Working with joins

Question 1

what is natural join in SF?

Answer 1

this is like a new concept
when you mention table a natural join table b
- all common columns using column names are joined
(more like a inner join but without explicitly mentioning column names on join condition)

Question 2

what are the types of sub queries?

Answer 2

correlated and uncorrelated.
correlated means - it references a column from outside of the subquery.
uncorrelated - it is independent and doesn’t reference a column from outside of the sub query.

Question 3

What are scalar vs non scalar subqueries?

Answer 3

scalar - returns 1 row/1 column only
non scalar - returns multiple rows/multiple column values.

If no rows qualify, scalar returns NULL
If no rows qualify, non scalar returns Zeroes and not null like scalar.

Question 4

What type of sub queries SF supports?

Answer 4

1. correlated scalar subqueries in where clause
2. uncorrelated scalar subqueries in anyplace a expression can be used.
3. EXISTS, ANY / ALL, and IN subqueries in WHERE clauses. These subqueries can be correlated or uncorrelated.

Question 5

how many rows uncorrelated scalar sub query brings?

Answer 5

1 row

Question 6

what is a CTE in SF?

Answer 6

CTE stands for common table expressions
It is nothing but a named sub query. 
It is defined using with function. 
with cte_name(col1, col2) as 
(
)
It is more like a temporary view.

Question 7

Can CTE name match table/view/M view name?

Answer 7

yes but not recommended. If it happens CTE tables precedence.

Question 8

what is a recursive CTE?

Answer 8

Recursive CTE references itself. It is mainly to process hierarchical data.

Question 9

what use case is very common for recursive CTE?

Answer 9

Hierarchical data - like managers/employees etc.

Question 10

what are the most common sections of recursive CTE?

Answer 10

Within the CTE
we should have anchor clause
union all
recursive clause referencing the anchor
and having a terminator clause.