Table expressions are named queries. We write an inner query that returns a relational result set, name it, and query it from an outer query.
T-SQL supports four forms of table expressions
- Derived Tables
- Common Table Expressions (CTEs)
- Inline Table-Valued Function
Upper tows are visible only to the statement that defines them and the last twos are database objects; therefore, these are reusable. Just like any other database objects these can be access controlled. Table Expressions are part of relational database, so it follows all rules of set theory, means that all columns returned by the inner query must have unique names. Also, the inner query is not allowed to have an ORDER BY clause as set has no order. But there is an exception of the last rule: If we use TOP or OFFSET-FETCH option in the inner query, the ORDER BY serves a meaning that is not related to presentation ordering; rather, it’s part of the filter’s specification. So if the inner query uses the TOP or OFFSET-FETCH option, it’s allowed to have an ORDER BY clause as well. But then the outer query has no presentation ordering guarantees if it doesn’t have its own ORDER BY clause.
A derived table is a form of table expression which is very close to a subquery—only a subquery that returns an entire table result. Derived tables are inner query in parentheses in the FROM clause of the outer query.
Difference between Derived tables and Subqueries:
– Derived Tables are used in the FROM clause
– Subqueries are used in the WHERE clause, but can also be used to select from one table and insert into another table).
Common Table Expression
CTE is a named table expression that is visible only to the statement that defines it. A common table expression (CTE) is similar to derived table. A query against a CTE involves three main parts:
The inner query
- The name you assign to the query and its columns
- The outer query
Unlike derive table the arrangement is quite different. With CTEs, you first name the CTE, then specify the inner query, and then the outer query—a much more modular approach.
Views and Inline Table-Valued Functions
Both views and Inline Table-Valued Functions are database objects. Both has existence after the query execution. Views are commonly used for data security restriction while Table-Valued Functions are users for returning tables. But the main differences between these two are,
- Table-Valued Function one can be parametrized but not indexed
- Views can be indexed but not parametrized
Table-Valued Functions Example: