Âíóòðåííèå ñîåäèíåíèÿ - ÷àñòü 3

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         

Ñîäåðæàíèå  Íàçàä  Âïåðåä

---

---

---

---