子查詢

子查詢是另一個 SQL 查詢中的查詢。子查詢也稱為內部查詢或內部選擇,包含子查詢的語句稱為外部查詢或外部選擇。

注意

  1. 子查詢必須括在括號內,
  2. ORDER BY 不能在子查詢中使用。
  3. 子查詢中不允許使用 BLOB,陣列,文字資料型別等影象型別。

子查詢可以與 where,from,select 子句以及 IN,比較運算子等一起使用 select,insert,update 和 delete 語句。

我們有一個名為 ITCompanyInNepal 的表,我們將在其上執行查詢以顯示子查詢示例:

StackOverflow 文件

示例: 使用 Select 語句的子查詢

使用 In 運算子和 where 子句:

SELECT *
FROM ITCompanyInNepal
WHERE Headquarter IN (SELECT Headquarter 
                      FROM ITCompanyInNepal
                      WHERE Headquarter = 'USA');

with comparison 運算子where 子句

SELECT *
FROM ITCompanyInNepal
WHERE NumberOfEmployee < (SELECT AVG(NumberOfEmployee) 
                          FROM ITCompanyInNepal
                      )

select 子句

SELECT   CompanyName,
         CompanyAddress,
         Headquarter,
         (Select SUM(NumberOfEmployee)
         FROM ITCompanyInNepal
         Where Headquarter = 'USA') AS TotalEmployeeHiredByUSAInKathmandu
FROM     ITCompanyInNepal 
WHERE    CompanyAddress = 'Kathmandu' AND Headquarter = 'USA'

帶有 insert 語句的子查詢

我們必須將 IndianCompany 表中的資料插入 ITCompanyInNepal。IndianCompany 的表格如下所示:

StackOverflow 文件

INSERT INTO ITCompanyInNepal
SELECT * 
FROM IndianCompany

帶有 update 語句的子查詢

假設由於美國公司的政策發生了一些變化,所有總部位於美國的公司決定從尼泊爾的所有美國公司解僱 50 名員工。

UPDATE ITCompanyInNepal
SET NumberOfEmployee = NumberOfEmployee - 50
WHERE Headquarter IN (SELECT Headquarter 
                      FROM ITCompanyInNepal 
                      WHERE Headquarter = 'USA')

帶有 Delete 語句的子查詢

假設總部位於丹麥的所有公司都決定將其公司從尼泊爾關閉。

DELETE FROM ITCompanyInNepal
WHERE Headquarter IN (SELECT Headquarter 
                     FROM ITCompanyInNepal
                     WHERE Headquarter = 'Denmark')