1 / 10

JOINS

JOINS. Oracle proprietary JOINS / ANSI/ISO Syntax. Equijoin (Oracle) / Natural Join (ANSI). Equijoin (Oracle) SELECT event_id, p. song_id, t.cd_number FROM d_play_list_items p, d_track_listings t WHERE p.song_id = t.song_id; Natural Join (ANSI) SELECT event_id, song_id , cd_number

gilda
Download Presentation

JOINS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. JOINS Oracle proprietary JOINS / ANSI/ISO Syntax

  2. Equijoin (Oracle) / Natural Join (ANSI) Equijoin (Oracle) SELECT event_id, p.song_id, t.cd_number FROM d_play_list_items p, d_track_listings t WHERE p.song_id = t.song_id; Natural Join (ANSI) SELECT event_id, song_id , cd_number FROM d_play_list_items NATURAL JOIN d_track_listings Natural Join este un equijoin care leaga cele doua tabele pe toate coloanele cu nume comun din cele doua tabele. Coloanele comune celor doua tabele trebuie sa aiba acelasi tip altfel rezulta eroare Nu e nevoie sa se foloseasca alias pt. tabele…

  3. Equijoin (Oracle) / Join…USING (ANSI) Equijoin (Oracle) SELECT event_id, p.song_id, t.cd_number FROM d_play_list_items p, d_track_listings t WHERE p.song_id = t.song_id; Join cu clauza USING (ANSI) SELECT event_id, song_id , cd_number FROM d_play_list_items JOIN d_track_listings USING (song_id) In clauza USING se trec coloanele pe care se face join-ul. Este utila atunci cand tabelele au mai multe coloane cu nume comun dar nu dorim in conditia de join sa folosim decat o parte din aceste coloane.

  4. Equijoin (Oracle) / Join…ON (ANSI) Equijoin (Oracle) SELECT event_id, p.song_id, t.cd_number FROM d_play_list_items p, d_track_listings t WHERE p.song_id = t.song_id; Join cu clauza ON (ANSI) SELECT event_id, song_id , cd_number FROM d_play_list_items p JOIN d_track_listings t ON (p.song_id=t.song_id); In clauza ON se pot specifica orice fel de conditii. Se pot lega tabele care nu au coloane cu acelasi nume

  5. Cartesian Product (Oracle) /Cross Join (ANSI) Cartesian Product (Oracle) – e ca un equijoin la care s-a omis conditia de join SELECT first_name, last_name, department_name FROM employees, departments; Cross join (ANSI) – are acelasi efect ca si Cartesian Product, adica "leaga" fiecare inregistrare din prima tabela cu fiecare inregistrare din a doua tabela SELECT first_name, last_name, department_name FROM employees CROSS JOIN departments;

  6. Nonequijoin (Oracle) / Join…ON (ANSI) Equijoin (Oracle) SELECT e.last_name, e.salary, j.grade_level FROM   employees e, job_grades j WHERE  e.salary        BETWEEN j.lowest_sal AND j.highest_sal Join cu clauza ON (ANSI) SELECT e.last_name, e.salary, j.grade_level FROM   employees e JOIN job_grades j ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal)

  7. Self Join (Oracle) / Join…ON (ANSI) Equijoin (Oracle) SELECT e.last_name||' '||e.first_name AS "Angajat", m.kast_name||' '||m.first_name AS "Manager" FROM employees e, employees m WHERE e.manager_id=m.employee_id Join cu clauza ON (ANSI) SELECT e.last_name||' '||e.first_name AS "Angajat", m.kast_name||' '||m.first_name AS "Manager" FROM employees e JOIN employees m ON (e.manager_id=m.employee_id)

  8. LEFT-OUTER JOIN Oracle Syntax SELECT e.last_name, e.department_id, d.department_name FROM   employees e, departments d WHERE  e.department_id = d.department_id (+); Afiseaza toti angajatii (e…) CU SAU FARA departament (d…, plusul e pe partea cu departamentul) Join cu clauza ON (ANSI) SELECT e.last_name, e.department_id, d.department_name FROM   employees e LEFT OUTER JOIN departments d ON (e.department_id=d.department_id); Afiseaza toate informatiile din tabela din STANGA (employees).

  9. RIGHT-OUTER JOIN Oracle Syntax SELECT e.last_name, e.department_id, d.department_name FROM   employees e, departments d WHERE  e.department_id(+) = d.department_id; Afiseaza toate departamentele (d…) CU SAU FARA angajati (e…, plusul e pe partea cu angajatii) Join cu clauza ON (ANSI) SELECT e.last_name, e.department_id, d.department_name FROM   employees e RIGHT OUTER JOIN departments d ON (e.department_id=d.department_id); Afiseaza toate informatiile din tabela din DREAPTA (departments).

  10. FULL-OUTER JOIN Oracle Syntax NU EXISTA FULL OUTER JOIN IN SINTAXA ORACLE PT. CA NU SE POATE PUNE + PE AMBELE PARTI ALE SEMNULUI DE EGALITATE Join cu clauza ON (ANSI) SELECT e.last_name, e.department_id, d.department_name FROM   employees e FULL OUTER JOIN departments d ON (e.department_id=d.department_id); Afiseaza toate informatiile din ambele tabele.

More Related