我對 SQL 查詢調整還很陌生。我一直在嘗試了解如何編寫等效查詢。在瀏覽J. Widom 教授的斯坦福線上影片講座時，她提到了一些不使用 .s 就無法由JOINsDISTINCT編寫的子查詢。例如，看到這個：

1. 4:45 / 20:13 - GPA 範例

SELECT GPA
FROM Student
WHERE sID in (select sID from Apply where major = 'CS');

2. 6:39 / 20:13 - 學生申請 CS 但未申請 EE

SELECT sID, sName
FROM Student
WHERE sID IN (select sID from Apply where major = 'CS')
 AND sID NOT IN (select sID from Apply where major = 'EE');

我的問題是如何知道使用子查詢編寫的 SQL 語句是否具有使用聯接編寫的等效語句。我很舒服，如果在答案中，有人喜歡使用關係代數表示法。

我在網上搜尋了很多，找不到合適的答案。

樣本數據

模式和表創建（對於 PostgreSQL）如下，

CREATE TEMP TABLE college AS
SELECT cname::text, state::text, enrollment::int
FROM ( VALUES
 ('Stanford', 'CA', 15000),
 ('Berkeley', 'CA', 36000),
 ('MIT', 'MA', 10000),
 ('Cornell', 'NY', 21000)
) AS College(cname, state, enrollment);

CREATE TEMP TABLE student AS
SELECT sid::int, sname::text, gpa::real, sizehs::int
FROM ( VALUES
 (123, 'Amy', 3.9, 1000),
 (234, 'Bob', 3.6, 1500),
 (345, 'Craig', 3.5, 500),
 (456, 'Doris', 3.9, 1000),
 (567, 'Edward', 2.9, 2000),
 (678, 'Fay', 3.8, 200),
 (789, 'Gary', 3.4, 800),
 (987, 'Helen', 3.7, 800),
 (876, 'Irene', 3.9, 400),
 (765, 'Jay', 2.9, 1500),
 (654, 'Amy', 3.9, 1000),
 (543, 'Craig', 3.4, 2000)
) AS Student(sid, sname, gpa, sizehs);

CREATE TEMP TABLE apply AS
SELECT sid::int, cname::text, major::text, decision::text
FROM ( VALUES
 (123, 'Stanford', 'CS', 'Y'),
 (123, 'Stanford', 'EE', 'N'),
 (123, 'Berkeley', 'CS', 'Y'),
 (123, 'Cornell', 'EE', 'Y'),
 (234, 'Berkeley', 'biology', 'N'),
 (345, 'MIT', 'bioengineering', 'Y'),
 (345, 'Cornell', 'bioengineering', 'N'),
 (345, 'Cornell', 'CS', 'Y'),
 (345, 'Cornell', 'EE', 'N'),
 (678, 'Stanford', 'history', 'Y'),
 (987, 'Stanford', 'CS', 'Y'),
 (987, 'Berkeley', 'CS', 'Y'),
 (876, 'Stanford', 'CS', 'N'),
 (876, 'MIT', 'biology', 'Y'),
 (876, 'MIT', 'marine biology', 'N'),
 (765, 'Stanford', 'history', 'Y'),
 (765, 'Cornell', 'history', 'N'),
 (765, 'Cornell', 'psychology', 'Y'),
 (543, 'MIT', 'CS', 'N')
) AS apply(sid, cname, major, decision);

我的問題是如何知道使用子查詢編寫的 SQL 語句是否具有使用聯接編寫的等效語句。我很舒服，如果在答案中，有人喜歡使用關係代數表示法。

好問題。

給出的第一個例子

所以讓我們看看她給出的第一個問題..

SELECT GPA
FROM Student
WHERE sID in (SELECT sID from Apply where major = 'CS');

然後，讓我們看一個類似的帶有連接的查詢，

SELECT *
FROM student
JOIN apply ON (student.sid = apply.sid AND major = 'CS');

我故意更改了第一個和第二個中的列。這裡的“GPA”，具體來說就是student.GPA.

如果如下，則相關子查詢不能重寫為簡單的 join，

1. 該SELECT語句具有聚合WHERE gpa in (SELECT max(gpa)..)（如果連接需要聚合或分組，則必須使用派生表或虛擬表：JOIN ( SELECT.. ) AS t.
2. 如果JOIN上面的條件不是唯一的，我們假設有student一個匹配的行同時具有公共sid和major=CS。結果集可以增長或縮小這個假設失敗。

你可以很容易地測試它..

SELECT sid, count(*)
FROM apply
WHERE major='CS'
GROUP BY sid
HAVING count(*) > 1;

這將返回兩行，兩個sids 都會在連接中被欺騙。

sid | count 
-----+-------
123 |     2
987 |     2

作為一個清晰的練習，您可以DISTINCT自己向下推，這可以再次使用JOIN.

SELECT GPA
FROM student
JOIN (
 SELECT DISTINCT sid, major
 FROM apply
) AS apply
 ON (student.sid = apply.sid AND major = 'CS');

甚至..

SELECT GPA
FROM student
JOIN (
 SELECT DISTINCT sid, major
 FROM apply
 WHERE major = 'CS'
) AS apply
 USING (sid);

給出的第二個例子

第二個例子，

SELECT sID, sName
FROM Student
WHERE sID IN (select sID from Apply where major = 'CS')
 AND sID NOT IN (select sID from Apply where major = 'EE');

正是第一個例子的犧牲品。然而NOT IN，

• 對 -ness 無關緊要，UNIQUE因為我們排除了結果。
• 此外，將 重寫NOT IN為反連接( LEFT OUTER JOIN ... ON NULL) 可能更快。
• 它將更有效地處理 apply.sid 為 NULL 的任何情況，包括沒有major = 'EE'

這是一個例子，

SELECT student.sID, sName
FROM Student
LEFT OUTER JOIN apply ON (student.sid = apply.sid AND major = 'EE')
WHERE student.sID IN (select sID from Apply where major = 'CS')
 AND apply.sid IS NULL;

總之

1. 連接（包括INNER JOINS）可以增加或減少結果集中的行數。

2. 如果JOIN條件從不為真，INNER JOIN則修剪 an 上的結果。

3. 如果同一行的結果多次為真，則結果是返回的行數增加。

4. 但是，連接連接兩個表。上t1 JOIN t2，

5. 如果t1, 和t2沒有重複的行，那麼SELECT t1.*, t2.*應該沒有重複。應該有所不同。

6. 如果其中一個t1或t2具有重複的行，則JOIN包含這些行的 a 將導致重複的輸出行。

7. 如果你SELECT是 的一個子集t1，或者是一個子集t2（就像你在上面只選擇 GPA 時所做的那樣），那麼可能會有一些看起來像是騙子的東西，儘管事實並非如此。如果您選擇student.GPA, apply.*了，您就會看到欺騙的原因。

引用自：https://dba.stackexchange.com/questions/159309