ÐÓÊÎÂÎÄÑÒÂÎ ÏÎ ÐÅËßÖÈÎÍÍÎÉ ÑÓÁÄ DB2

         

ÑËÓ×ÀÉ ÈÑÏÎËÜÇÎÂÀÍÈß ÎÄÍÎÉ È ÒÎÉ ÆÅ ÒÀÁËÈÖÛ Â ÏÎÄÇÀÏÐÎÑÅ È ÂÍÅØÍÅÌ ÇÀÏÐÎÑÅ


Âûäàòü íîìåðà ïîñòàâùèêîâ, êîòîðûå ïîñòàâëÿþò ïî êðàéíåé ìåðå îäíó äåòàëü, ïîñòàâëÿåìóþ ïîñòàâùèêîì 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';



Ñîäåðæàíèå ðàçäåëà