Âíóòðåííèå ñîåäèíåíèÿ
Âíóòðåííåå ñîåäèíåíèå âîçâðàùàåò òîëüêî òå ñòðîêè, äëÿ êîòîðûõ óñëîâèå ñîåäèíåíèÿ ïðèíèìàåò çíà÷åíèå true.
SELECT first_name, last_name, department
FROM employee, department
WHERE job_code = "VP" ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ,
ñîñòîÿùèõ â äîëæíîñòè “âèöå-
ïðåçèäåíò”, à òàêæå íàçâàíèÿ
èõ îòäåëîâ
FIRST_NAME LAST_NAME DEPARTMENT
=============== ================ ======================
Robert Nelson Corporate Headquarters
Mary S. MacDonald Corporate Headquarters
Robert Nelson Sales and Marketing
Mary S. MacDonald Sales and Marketing
Robert Nelson Engineering
Mary S. MacDonald Engineering
Robert Nelson Finance
Mary S. MacDonald Finance
...
Ýòîò çàïðîñ (“áåç ñîåäèíåíèÿ”) âîçâðàùàåò íåâåðíûé ðåçóëüòàò, òàê êàê èìåþùèåñÿ ìåæäó òàáëèöàìè ñâÿçè íå çàäåéñòâîâàíû. Îòñþäà è ïîÿâëÿåòñÿ äóáëèðîâàíèå èíôîðìàöèè â ðåçóëüòèðóþùåé òàáëèöå. Ïðàâèëüíûé ðåçóëüòàò äàåò çàïðîñ ñ èñïîëüçîâàíèåì îïåðàöèè ñîåäèíåíèÿ:
SELECT first_name, last_name, department
FROM employee, department
WHERE job_code = "VP"
AND employee.dept_no = department.dept_no
 |
|||
 |
èìåíà òàáëèö
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ,
ñîñòîÿùèõ â äîëæíîñòè “âèöå-
ïðåçèäåíò”, à òàêæå íàçâàíèÿ
èõ îòäåëîâ
FIRST_NAME LAST_NAME DEPARTMENT
=============== ================ ======================
Robert Nelson Engineering
Mary S. MacDonald Sales and Marketing
 âûøåïðèâåäåííîì çàïðîñå èñïîëüçîâàëñÿ ñïîñîá íåïîñðåäñòâåííîãî óêàçàíèÿ òàáëèö ñ ïîìîùüþ èõ èìåí. Âîçìîæåí (à èíîãäà è ïðîñòî íåîáõîäèì) òàêæå ñïîñîá óêàçàíèÿ òàáëèö ñ ïîìîùüþ àëèàñîâ (ïñåâäîíèìîâ). Ïðè ýòîì àëèàñû îïðåäåëÿþòñÿ â ïðåäëîæåíèè FROM çàïðîñà SELECT è ïðåäñòàâëÿþò ñîáîé ëþáîé äîïóñòèìûé èäåíòèôèêàòîð, íàïèñàíèå êîòîðîãî ïîä÷èíÿåòñÿ òàêèì æå ïðàâèëàì, ÷òî è íàïèñàíèå èìåí òàáëèö. Ïîòðåáíîñòü â àëèàñàõ òàáëèö âîçíèêàåò òîãäà, êîãäà íàçâàíèÿ ñòîëáöîâ, èñïîëüçóåìûõ â óñëîâèÿõ ñîåäèíåíèÿ äâóõ (èëè áîëåå) òàáëèö, ñîâïàäàþò, à íàçâàíèÿ òàáëèö ñëèøêîì äëèííû...
Çàìå÷àíèå 1: â îäíîì çàïðîñå íåëüçÿ ñìåøèâàòü èñïîëüçîâàíèå íàïèñàíèÿ èìåí òàáëèö è èõ àëèàñîâ.
Çàìå÷àíèå 2: àëèàñû òàáëèö ìîãóò ñîâïàäàòü ñ èõ èìåíàìè.
SELECT first_name, last_name, department
WHERE job_code = "VP"
àëèàñû òàáëèö
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ,
ñîñòîÿùèõ â äîëæíîñòè “âèöå-
ïðåçèäåíò”, à òàêæå íàçâàíèÿ
èõ îòäåëîâ
FIRST_NAME LAST_NAME DEPARTMENT
=============== ================ ======================
Robert Nelson Engineering
Mary S. MacDonald Sales and Marketing
À âîò ïðèìåð çàïðîñà, ñîåäèíÿþùåãî ñðàçó òðè òàáëèöû:
SELECT first_name, last_name, job_title,
department
FROM employee e, department d, job j
WHERE d.mngr_no = e.emp_no
AND e.job_code = j.job_code
AND e.job_grade = j.job_grade
AND e.job_country = j.job_country
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ
ñ íàçâàíèÿìè èõ äîëæíîñòåé
è íàçâàíèÿìè îòäåëîâ
FIRST_NAME LAST_NAME JOB_TITLE DEPARTMENT
========== ============ ======================= ======================
Robert Nelson Vice President Engineering
Phil Forest Manager Quality Assurance
K. J. Weston Sales Representative Field Office: East Coast
Katherine Young Manager Customer Support
Chris Papadopoulos Manager Research and Development
Janet Baldwin Sales Co-ordinator Pacific Rim Headquarters
Roger Reeves Sales Co-ordinator European Headquarters
Walter Steadman Chief Financial Officer Finance
 äàííîì ïðèìåðå ïîñëåäíèå òðè óñëîâèÿ íåîáõîäèìû â ñèëó òîãî, ÷òî ïåðâè÷íûé êëþ÷ â òàáëèöå JOB ñîñòîèò èç òðåõ ïîëåé - ñì. ðèñ.1.
Ìû ðàññìîòðåëè âíóòðåííèå ñîåäèíåíèÿ ñ èñïîëüçîâàíèåì ñòàíäàðòà ANSI-89. Òåïåðü îïèøåì íîâûé (ANSI-92) ñòàíäàðò:
· óñëîâèÿ ñîåäèíåíèÿ çàïèñûâàþòñÿ â ïðåäëîæåíèè FROM, â êîòîðîì ñëåâà è ñïðàâà îò çàðåçåðâèðîâàííîãî ñëîâà “JOIN” óêàçûâàþòñÿ ñîåäèíÿåìûå òàáëèöû;
· óñëîâèÿ ïîèñêà, îñíîâàííûå íà ïðàâîé òàáëèöå, ïîìåùàþòñÿ â ïðåäëîæåíèå ON;
· óñëîâèÿ ïîèñêà, îñíîâàííûå íà ëåâîé òàáëèöå, ïîìåùàþòñÿ â ïðåäëîæåíèå WHERE.
SELECT first_name, last_name, department
FROM employee e JOIN department d
ON e.dept_no = d.dept_no
AND department = "Customer Support"
WHERE last_name starting with "P"
ïîëó÷èòü ñïèñîê ñëóæàùèõ
(à çàîäíî è íàçâàíèå îòäåëà),
ÿâëÿþùèõñÿ ñîòðóäíèêàìè îòäåëà
“Customer Support”, ôàìèëèè êîòî-
ðûõ íà÷èíàþòñÿ ñ áóêâû “P”
FIRST_NAME LAST_NAME DEPARTMENT
============= =============== ===================
Leslie Phong Customer Support
Bill Parker Customer Support
