子查詢
子查詢是另一個 SQL 查詢中的查詢。子查詢也稱為內部查詢或內部選擇,包含子查詢的語句稱為外部查詢或外部選擇。
注意
- 子查詢必須括在括號內,
- ORDER BY 不能在子查詢中使用。
- 子查詢中不允許使用 BLOB,陣列,文字資料型別等影象型別。
子查詢可以與 where,from,select 子句以及 IN,比較運算子等一起使用 select,insert,update 和 delete 語句。
我們有一個名為 ITCompanyInNepal 的表,我們將在其上執行查詢以顯示子查詢示例:
示例: 使用 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 的表格如下所示:
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')