SQL server-Table Expression Basics

Table expression

Table expressions do not have any physical instantiation, they are virtual when querying table expressions, internal queries are non-nested, in other words, external queries and internal queries are merged directly into a query of an underlying object, and the benefits of using table expressions are often related to the logical aspects of the code, regardless of the performance of the code-excerpt from SQL Basic Server 2012 Tutorials. We must meet the following 3 requirements when using table expressions, or we will get an error. Here's a brief introduction to the 4 types in the following table expressions.

(1) The order cannot be guaranteed.

(2) All columns must have a name.

(3) All column names must be unique.

Derived tables

Derived tables (also known as subquery tables) are defined in the FROM clause of an external query, and they exist in an external query. Once the external query is complete, the derived table disappears. Let's look at an example of a simple derived table.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012goselect *from (SELECT * from sales.customers WHERE country = N ' USA ') as usacusts;

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Let's take a look at the conditions that have been explicitly stated in the table expression query, and then we'll make the following query:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012goselect *from (SELECT * from sales.customers WHERE country = N ' USA ' ORDER by CustID) as usacusts;

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/589642/201612/589642-20161216213903073-582952742. PNG "style=" margin:0px;padding:0px;border:0px; "/>

When we add an order by in a subquery, the above error occurs, which means that the table expression above requires the 1th, the table expression as the relational table, because the relationship originates from the set theory, so the order of the output data cannot be guaranteed, see the SQL Server 2012 Basic tutorial that says, I also understand that the real reason is still not understandable, anyway in the table expression do not do order by. About the requirements of the 2nd and 3rd do not have to say, such as the above at this time the table can not be an alias will be error, and when you join the table, the column fields in the table must have the same, in order to ensure unique, we must alias for the column name to solve the not unique problem. One of the benefits of using table expressions is that in any clause of an external query, you can reference the column aliases that are allocated in the SELECT clause of an internal query, which can help us bypass the query clause before the SELECT clause is logically processed (such as where, GROUP By) cannot refer to the actual problem of the column alias assigned in the SELECT clause, what exactly does it mean, we know that when a regular query is made, the where, group by is done before the Select, so it leads us to the column in select through where, group By cannot be referenced, let's take a look at the following example.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012goselect Year (OrderDate) as OrderYear, COUNT (DISTINCT CustID) as Custidsfrom Sales.ordersgroup by OrderYear

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

As above, we are grouping the OrderYear in select with group by, but the group by operation is preceded by the Select and causes the following error to occur.

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/589642/201612/589642-20161216215242229-261076901. PNG "style=" margin:0px;padding:0px;border:0px; "/>

To solve this problem we can query by a derived table in the table expression

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012goselect orderyear, COUNT (DISTINCT CustID) as Custidsfrom (SELECT year (OrderDate) as OrderYear, CustID from S Ales. Orders) as Sogroup by OrderYear

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

For derived tables you can reference parameters to store variables such as procedures or functions, or input parameters, and derived tables can be nested, as follows:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012goselect orderyear, Numcustsfrom (SELECT orderyear, COUNT (DISTINCT CustID) as numcusts from (S  Elect year (OrderDate) as OrderYear, CustID from Sales.orders) as D1 GROUP by OrderYear) as D2where numcusts > 70;

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/589642/201612/589642-20161216222138308-1939916511. PNG "style=" margin:0px;padding:0px;border:0px; "/>

When multiple tables are nested this way, the code becomes more complex, and lengthy code is not conducive to maintenance and can lead to errors, while reducing the readability of the code. At this point we can use the 2nd form of the table expression CTE.

Common table Expressions (CTE)

The CTE is defined by the WITH statement and has the following common form.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

With <cte_name>[(<target_column_list>)]as (<inner_query_defining_CTE>) <outer_query_again St_cte>

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Let's take a look at a simple example of a CTE

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012gowith usacusts as (select CustID, CompanyName from sales.customers WHERE country = N ' USA ') SELECT * F ROM usacusts

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/589642/201612/589642-20161216221121776-1114369313. PNG "style=" margin:0px;padding:0px;border:0px; "/>

As with derived tables, the CTE disappears as soon as the external query is completed. We can also use parameters in the CTE, as follows:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012godeclare @empid as INT = 3; With C as (select Year (OrderDate) as OrderYear, CustID from sales.orders WHERE empid = @empid) SELECT orderyear, C Ount (DISTINCT CustID) as Numcustsfrom CGROUP by OrderYear

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

We can similarly implement a derived table-like nesting, as follows:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012gowith C1 as (select Year (OrderDate) as OrderYear, CustID from Sales.orders), C2 as (select OrderYear, COUNT (DISTINCT CustID) as numcusts from C1 GROUP by OrderYear) SELECT OrderYear, Numcustsfrom c2where numcusts > 7 0

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/589642/201612/589642-20161216222155167-1979971083. PNG "style=" margin:0px;padding:0px;border:0px; "/>

Here we use the CTE to achieve the same result as the derived table, the derived table and the CTE are actually only semantically different, but the main advantage relative to the derived table is that there is no need for multiple nesting like the derived table, and the CTE is not nested as long as it is defined, and each CTE appears in the code in a modular way. This is a modular approach that greatly improves the readability and maintainability of the code compared to nested derived tables, and if more than one table needs to be nested with a CTE to make it more refreshing and helpful to the clarity of the code. Another advantage of the derived table is that the CTE already existed prior to the FROM clause of the external query, so that multiple instances of the same CTE could be referenced.

Views (view)

Views and inline table-valued functions are two types of table expressions that can be reused, and their definitions are stored as database objects, which are permanent portions of the database and are deleted from the database only when they are explicitly deleted. Let's look at how to create a view and use a view.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012goif object_id (' sales.usacusts ') are not NULL DROP VIEW sales.usacusts; Gocreate VIEW Sales.usacustsasselect CustID, CompanyName, ContactName, ContactTitle, [Address]from Sales.customerswhere country = N ' USA ' GO

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

After creating the view, the view object already exists in the database, so we can query the view again.

Use Tsql2012goselect * from sales.usacusts

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/589642/201612/589642-20161216223502886-175770325. PNG "style=" margin:0px;padding:0px;border:0px; "/>

Inline table-valued function (TVF)

An inline table-valued function is a reusable table expression that supports input parameters. All aspects other than support input parameters are similar to views. Let's see how to create an inline table-valued function.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use tsql2012goif object_id (' dbo. GetCustOrders ') is not a NULL DROP FUNCTION dbo. GetCustOrders; Gocreate FUNCTION dbo. GetCustOrders (@cid as INT) RETURNS tableas RETURN SELECT OrderID, CustID, Empid, OrderDate, RequiredDate, ShippedDate, ShipperID, ShipCity, ShipAddress, ShipRegion, freight from sales.orders WHERE CustID = @cidGO

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Now that we've created TVF, we're going to call the custom TVF

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use tsql2012goselect OrderID, Custidfrom dbo. GetCustOrders (1) as O;

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/589642/201612/589642-20161216224330433-897679919. PNG "style=" margin:0px;padding:0px;border:0px; "/>

Above we provide an alias for the table expression, although not required, but it is recommended because it makes the code more readable and less error--. In this section we review the 4 ways in which table expressions are expressed, and we make a conclusion about these 4 forms of table expressions.

(1) Table expressions can simplify code, improve the maintainability of code, and encapsulate query logic.

(2) When you need to use a table expression and do not intend to reuse its definition, you can use a derived table or a CTE, and the CTE has more advantages over derived tables without nesting a CTE like a derived table, using a CTE to make your code more modular and maintainable, and also referencing multiple instances of the same CTE. This is not possible with derived tables.

(3) When you need to use a table expression and need to define a reusable table expression, you can use a view or inline table-valued function, or you can use a view when you don't need to support input parameters, or you should use an inline table-valued function (TVF).

SQL server-Table Expression Basics