ÑËÓ×ÀÉ ÈÑÏÎËÜÇÎÂÀÍÈß ÎÄÍÎÉ È ÒÎÉ ÆÅ ÒÀÁËÈÖÛ Â ÏÎÄÇÀÏÐÎÑÅ È ÂÍÅØÍÅÌ ÇÀÏÐÎÑÅ
Âûäàòü íîìåðà ïîñòàâùèêîâ, êîòîðûå ïîñòàâëÿþò ïî êðàéíåé ìåðå îäíó äåòàëü, ïîñòàâëÿåìóþ ïîñòàâùèêîì S2.
SELECT DISTINCT ÍÎÌÅÐ_ÏÎÑÒÀÂÙÈÊÀ
FROM SP
WHERE ÍÎÌÅÐ_ÄÅÒÀËÈ IN
(SELECT ÍÎÌÅÐ_ÄÅÒÀËÈ
FROM SP
WHERE ÍÎÌÅÐ_ÏÎÑÒÀÂÙÈÊÀ = 'S2');
Ðåçóëüòàò:
ÍÎÌÅÐ_ÏÎÑÒÀÂÙÈÊÀ
S1
S2
S3
S4
Îòìåòèì çäåñü, ÷òî ññûëêà íà SP â ïîäçàïðîñå îçíà÷àåò íå òî æå ñàìîå, ÷òî ññûëêà íà SP âî âíåøíåì çàïðîñå.  äåéñòâèòåëüíîñòè, äâà èìåíè SP îáîçíà÷àþò ðàçëè÷íûå ïåðåìåííûå. ×òîáû ýòîò ôàêò ñòàë ÿâíûì, ìîæíî èñïîëüçîâàòü ïñåâäîíèìû:
SELECT DISTINCT SPX. ÍÎÌÅÐ_ÏÎÑÒÀÂÙÈÊÀ
FROM SP SPX
WHERE SPX. ÍÎÌÅÐ_ÄÅÒÀËÈ IN
(SELECT SPY. ÍÎÌÅÐ_ÄÅÒÀËÈ
FROM SP SPY
WHERE SPY. ÍÎÌÅÐ_ÏÎÑÒÀÂÙÈÊÀ ='S2');
Ýêâèâàëåíòíûé çàïðîñ ñ èñïîëüçîâàíèåì ñîåäèíåíèÿ èìååò âèä;
SELECT DISTINCT SPX. ÍÎÌÅÐ_ÏÎÑÒÀÂÙÈÊÀ
FROM SP SPX, SP SPY
WHERE SPX. ÍÎÌÅÐ_ÄÅÒÀËÈ = SPY. ÍÎÌÅÐ_ÄÅÒÀËÈ
AND SPY. ÍÎÌÅÐ_ ÏÎÑÒÀÂÙÈÊÀ = 'S2';