SQL Table Expression

Introduction

A table expression is a named query expression that represents a valid relational table. MSSQL supports table expressions of type 4: derived tables, common expressions (CTE), views, and inline table-valued functions. Table expressions are not physically real objects, they are virtual. Queries for table expressions are converted inside the database engine to queries against the underlying objects. The benefits of using table expressions are usually reflected in the logical aspect of the code, not the new energy aspect.

To effectively define any type of table expression, the query statement must meet three requirements: there is no guarantee that there is a bit of order, all columns must have a name, and all column names must be unique.

1. Derived tables

Derived tables (also known as table subqueries) are defined in the FROM clause of an external query. A derived table exists in the outer query that defines it, as long as the outer query ends and the derived table does not exist. The query statement for the derived table needs to be written in a pair of parentheses, followed by the name of the AS clause and the derived table.

For example, a simple derived table: The following code returns the number of direct subordinates with an employee ID of 2.

SELECTa.mgrid,numofsubordinate from (    SELECTMgrid,COUNT(*) asnumofsubordinate from [TSQLFundamentals2008].[HR].[Employees]    WHEREMgrid is  not NULL    GROUP  byMgrid) asAWHEREA.mgrid=2

The problem with a derived table is that it increases the complexity of the code when it is nested before multiple derived tables, and because multiple instances of the same derived table cannot be referenced, and therefore have to maintain multiple copies of the same query definition, which makes the code verbose.

For example, define the same derived table repeatedly: The following code returns those employee IDs that are equal to the number of direct subordinates with an employee ID of 2.

SELECTB.mgrid from (    SELECTa.mgrid,numofsubordinate from     (        SELECTMgrid,COUNT(*) asnumofsubordinate from [TSQLFundamentals2008].[HR].[Employees]        WHEREMgrid is  not NULL        GROUP  byMgrid) asAWHEREA.mgrid=2)  asAJOIN(    SELECTMgrid,COUNT(*) asnumofsubordinate from [TSQLFundamentals2008].[HR].[Employees]    WHEREMgrid is  not NULL    GROUP  byMgrid) asB onA.numofsubordinate=B.numofsubordinate andA.mgrid<>B.mgrid

Common Expressions (CT E)                                                                                                                                                                               

SQL Table Expression