Âíåøíèå ñîåäèíåíèÿ
Íàïîìíèì, ÷òî âíóòðåííåå ñîåäèíåíèå âîçâðàùàåò òîëüêî òå ñòðîêè, äëÿ êîòîðûõ óñëîâèå ñîåäèíåíèÿ ïðèíèìàåò çíà÷åíèå true. Èíîãäà òðåáóåòñÿ âêëþ÷èòü â ðåçóëüòèðóþùèé íàáîð áîëüøåå êîëè÷åñòâî ñòðîê.
Âñïîìíèì, çàïðîñ âèäà
SELECT first_name, last_name, department
FROM employee e, department d
WHERE e.dept_no = d.dept_no
âîçâðàùàåò òîëüêî òå ñòðîêè, äëÿ êîòîðûõ óñëîâèå ñîåäèíåíèÿ (e.dept_no = d.dept_no) ïðèíèìàåò çíà÷åíèå true.
Âíåøíåå ñîåäèíåíèå âîçâðàùàåò âñå ñòðîêè èç îäíîé òàáëèöû è òîëüêî òå ñòðîêè èç äðóãîé òàáëèöû, äëÿ êîòîðûõ óñëîâèå ñîåäèíåíèÿ ïðèíèìàåò çíà÷åíèå true. Ñòðîêè âòîðîé òàáëèöû, íå óäîâëåòâîðÿþùèå óñëîâèþ ñîåäèíåíèÿ (ò.å. èìåþùèå çíà÷åíèå false), ïîëó÷àþò çíà÷åíèå null â ðåçóëüòèðóþùåì íàáîðå.
Ñóùåñòâóåò äâà âèäà âíåøíåãî ñîåäèíåíèÿ: LEFT JOIN è RIGHT JOIN.
 ëåâîì ñîåäèíåíèè (LEFT JOIN) çàïðîñ âîçâðàùàåò âñå ñòðîêè èç ëåâîé òàáëèöû (ò.å. òàáëèöû, ñòîÿùåé ñëåâà îò çàðåçåðâèðîâàííîãî ñëîâîñî÷åòàíèÿ “LEFT JOIN”) è òîëüêî òå èç ïðàâîé òàáëèöû, êîòîðûå óäîâëåòâîðÿþò óñëîâèþ ñîåäèíåíèÿ. Åñëè æå â ïðàâîé òàáëèöå íå íàéäåòñÿ ñòðîê, óäîâëåòâîðÿþùèõ çàäàííîìó óñëîâèþ, òî â ðåçóëüòàòå îíè çàìåùàþòñÿ çíà÷åíèÿìè null.
Äëÿ ïðàâîãî ñîåäèíåíèÿ - âñå íàîáîðîò.
SELECT first_name, last_name, department
FROM employee e LEFT JOIN department d
ON e.dept_no = d.dept_no
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ
è íàçâàíèå èõ îòäåëîâ,
âêëþ÷àÿ ñîòðóäíèêîâ, åùå
íå íàçíà÷åííûõ íè â êàêîé îòäåë
FIRST_NAME LAST_NAME DEPARTMENT
=============== ============== =====================
Robert Nelson Engineering
Bruce Young Software Development
Kim Lambert Field Office: East Coast
Leslie Johnson Marketing
Phil Forest Quality Assurance
...
 äàííîì çàïðîñå âñå ñîòðóäíèêè îêàçàëèñü ðàñïðåäåëåíû ïî îòäåëàì, èíà÷å íàçâàíèÿ îòäåëîâ çàìåñòèëèñü áû çíà÷åíèåì null.
À âîò ïðèìåð ïðàâîãî ñîåäèíåíèÿ:
SELECT first_name, last_name, department
FROM employee e RIGHT JOIN department d
ON e.dept_no = d.dept_no
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ
è íàçâàíèå èõ îòäåëîâ,
âêëþ÷àÿ îòäåëû, â êîòîðûå åùå
íå íàçíà÷åíû ñîòðóäíèêè
FIRST_NAME LAST_NAME DEPARTMENT
=============== ============= =========================
Terri Lee Corporate Headquarters
Oliver H. Bender Corporate Headquarters
Mary S. MacDonald Sales and Marketing
Michael Yanowski Sales and Marketing
Robert Nelson Engineering
Kelly Brown Engineering
Stewart Hall Finance
Walter Steadman Finance
Leslie Johnson Marketing
Carol Nordstrom Marketing
<null> <null> Software Products Div.
Bruce Young Software Development
...
 ðåçóëüòèðóþùèé íàáîð âõîäèò è îòäåë “Software Products Div.” (à òàêæå îòäåë “Field Office: Singapore”, íå ïðåäñòàâëåííûé çäåñü), â êîòîðîì åùå íåò íè îäíîãî ñîòðóäíèêà.