数据库杂记

| 分类 未分类 

记录一些概念性的东西。

1 Subquery and Dreived-Tables

1.1 Subquery

A subquery is defined as any query that appears within another SQL command. These can be further broken down into two kinds of subqueries:

  • stand-alone sub-queries:
    The ones that can run independent of the command that contains it
  • correlated sub-queries:
    which relate to fields from the containing command.

The most common use for subqueries is filtering data in the WHERE clause of a SQL command or in the column list.

Here’s a subquery that retrieves a list of all customers who placed orders in the specified year. The main query uses that list to find the reverse - those who didn't order in that year:

1: SELECT CompanyName
2: FROM   Customers
3: WHERE  CustomerID NOT IN (SELECT CustomerID
4:                           FROM   Orders
5:                           WHERE  YEAR(OrderDate) = 2003);

1.2 Derived-Tables

Compare the previous query to the following one:

1: SELECT u.last_name,
2:        a.state
3: FROM   User AS u,
4:        (SELECT user_id, state FROM Address) AS a
5: WHERE  u.id = a.user_id;

The above query selects directly from the derived table created by the subquery.


上一篇     下一篇