Joins
This presentation is the property of its rightful owner.
Sponsored Links
1 / 10

JOINS PowerPoint PPT Presentation


  • 42 Views
  • Uploaded on
  • Presentation posted in: General

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

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

JOINS

Oracle proprietary JOINS / ANSI/ISO Syntax


Equijoin oracle natural join ansi

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

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)


Self join oracle join on ansi

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)


Left outer join

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


Right outer join

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


Full outer join

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.


  • Login