子查询

SQL 还允许创建子查询,既嵌套在其他查询中的查询

2. 为什么要创建子查询?

2.1 利用子查询进行过滤

可以把一条SELECT 语句的返回结果用于另一条SELECT 语句的WHERE 语句中

SELECT cust_id 
FROM Orders
WHERE order_num in (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

SELECT 子查询总是从内向外处理

  • 只能返回单列

    作为子查询的SELECT 语句只能查询单个列,企图检索多个列将返回错误

2.2 作为计算字段使用子查询

SELECT cust_name,
        cust_state,
        (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_iid = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

results matching ""

    No results matching ""