已归录

返回单行单列子查询又被称为 Scalar Subqueries。
A scalar subquery expression is a subquery that returns exactly one column value from one row.

标量子查询可以被使用在哪些地方:

• The condition and expression part of DECODE and CASE
• All clauses of SELECT except GROUP BY
• The SET clause and WHERE clause of an UPDATE statement

示例1：Scalar subqueries in CASE expressions

SELECT employee_id, last_name,
    (CASE
    WHEN department_id =
        (SELECT department_id 
        FROM departments
        WHERE location_id = 1800)
    THEN 'Canada' ELSE 'USA' END) location
FROM employees;

示例2：Scalar subqueries in the SELECT statement

select department_id, department_name,
    (select count(*)
    from employees e
    where e.department_id = d.department_id) as emp_count
from departments d;

简单地说，标量子查询就是 select 里面套 select，例如：
select ... (select b.x from b from b where b.id=a.id) from a;

a:b = 1:1 或者 n: 1，如果 a:b 是 1:n，可以 rownum =1 或者 max()/min()
如果 b 要返回多外列要以用 ||

标量子查询一般都有严重的性能问题，只需要改成 left join 就可以了。