1 / 10

# JOINS - PowerPoint PPT Presentation

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' JOINS' - qiana

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### 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

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…

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.

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

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;

Nonequijoin (Oracle) / Join…ON (ANSI)

Equijoin (Oracle)

WHERE  e.salary

BETWEEN j.lowest_sal AND j.highest_sal

Join cu clauza ON (ANSI)

FROM   employees e JOIN job_grades j

ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal)

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)

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).

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).

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.