Let’s talk
about table expressions. I will start with Derrived Tables. They are like
subqueries. The return a table result. You put the query inside parentheses
after the FROM keyword. The results are only visible to the outer query. In
Oracle, we call these inline views.
Next let’s
discuss Common Table Expressions (CTEs). They are another type of table
expression. Like derived tables, they are only visible to the outer queries.
You put the expression in parentheses after the WITH keyword at the beginning
of your query. You can have multiple CTEs in the WITH clause separated by
commas. You could also make them recursive if you connect them with UNION ALL.
Next there
are Views. You are not allowed to pass parameters to a view. Normally just the
SQL of the view is stored internally. That SQL is executed every time the view
is accessed to generate real time results.
Finally, there are Inline Table Valued
Functions. These can have input parameters. The function should be declared as
RETURNS TABLE. Then in the body of the function you RETURN a SELECT statement
results.
