06 - Displaying data from multiple tables

Llojet e JOIN

• Cross joins

• Natural joins

• USING clause

• Full (or two-sided) outer joins

• Arbitrary join conditions for outer joins

Sintaksa e pergjithshme e JOIN

SELECT table1.column, table2.column

FROM table1

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2

ON (table1.column_name = table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column_name = table2.column_name)]|

[CROSS JOIN table2];

CROSS JOIN (Prodhimi kartezian i dy bashkesive)

Cross JOIN kthen prodhimin kartezian midis tabelave qe jane pjese e JOIN

Per shembull komanda me poshte

select

employees.first_name,employees.last_name,employees.department_id,

departments.department_id,departments.department_name

from

employees cross join departments

order by first_name;

do te kthente te gjithe kombinimet e mundshme midis punonjesve dhe departamenteve.

Vini re rezultatin, cila eshte dobia e ketij informacioni?

Ushtrim

Afishoni te gjithe pozicionet e mundshme te punes ne sejcilin nga departamentet ne formatin e meposhtem:

zgjidhje

select d.department_name,j.job_title

from jobs j cross join departments d

order by d.department_name;

Natural JOIN

• Klauzola NATURAL JOIN bazohet ne kollona qe kane te njejtin emer.

• Kthen te gjithe rreshtat ne te dy tabelat qe kane te njejten vlere te kollones me te njejtin emei.

• Nese kollonat qe kane te njejtin emer ne te dy tabelat kane tipe te ndryshme(jo kompatibel) te dhenash atehere kthehet nje mesazh gabimi.

Per shembull

SELECT department_id, department_name,

location_id, city

FROM departments

NATURAL JOIN locations ;

do te kthente

Ushtrim

Due perdorur natural join ndertoni nje query qe kthen rezultatin e meposhtem:

Zgjidhje

select

First_name,Last_name,Department_id,Department_name

from employees NATURAL JOIN departments;

JOIN me klauzolen using

Nese ka disa kollona me te njejtin emer klauzola NATURAL JOIN mund te modofikohet duke perdorur klauzolen USING per te percaktuar cilat kollona do te perdoren ne equijoin

Nuk mund te perdoret emri i tabeles perpara kollones ne kollonat qe do te perdoren per JOIN.

Per shembull

select

First_name,Last_name,Department_id,Department_name

from employees join departments

using (department_id);

Do te kthente

Ushtrim

Duke perdorur JOIN .. USING ,ndertoni nje komande select qe kthen kollonat e meposhtme:

Zgjidhje

select

First_name,Last_name,Department_id,JOB_TITLE

from employees join jobs

using (job_id);

Table ALIAS

Trajtimi i kollonave me te njejtin emer qe ndodhen ne tabela te ndryshme

• Emrat e tabelave perpara emrave te kollonave duhet te perdoren per t'Ju referuar kollonave ne listen select te cilat kane te njejtin emer ne dy tabela te JOIN.

• Emrat e tabelave perpara emrave te kollonave ndikojne ne permiresimin e performances.

• Eshte mire qe te perdorren Alias-et per kollonat e listes select qe kane te njejtin emer

Shembull

Komanda me poshte perdor ALIAS per tabelat ne nje JOIN

SELECT e.employee_id, e.last_name,

d.location_id, department_id

FROM employees e JOIN departments d

USING (department_id) ;

Rezultati i komandes do te kishte strukturen e meposhtme:

JOIN me kaluzolen ON

Kushti i join tek natural join eshte nje

Per shembull, komanda me poshte :

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id);

afishon te dhenat e punonjesve dhe vendndodhjen e zyres se tyre:

Perdorimi i kushteve shtese ne nje JOIN

Pervec kushtit te JOIN mund te vendosen gijthashtu edhe kushte te tjera llogjike qe lidhen me kushtin e JOIN me lidhezat llogjike.

Per shembull, komanda

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

AND e.manager_id = 149 ;

do te kthente vetem vartesit e punonjesit me ID =149.

Kushtet shtese mund te vendosen gjithashtu duke perdorur klauzolen where. Komanda me siper mund shkruhej gjithashtu duke perdorur klauzolen where:

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

where e.manager_id = 149 ;

JOIN me disa tabela

Eshte e mundur qe te realizojme JOIN me disa tabela ne kete rast kushtet e JOIN vendosen njeri pas tjetrit.

Shembulli me poshte ilustron sintaksen:

SELECT employee_id, city, department_name

FROM employees e

JOIN departments d

ON d.department_id = e.department_id

JOIN locations l

ON d.location_id = l.location_id;

INNER DHE OUTER JOIN

USHTRIM

Te afishojme per cdo punonjes

zgjidhje

select vartes.first_name as emri,

vartes.last_name as mbiemri,

shefat.first_name as emri_shefit,

shefat.last_name as mbiemri_shefit

from employees vartes inner join employees shefat

on vartes.manager_id=shefat.employee_Id;

OUTER JOIN

A ka nga shembulli me lart ndonje punonjes qe nuk shfaqet?

LEFT OUTER JOIN

select vartes.first_name as emri,

vartes.last_name as mbiemri,

shefat.first_name as emri_shefit,

shefat.last_name as mbiemri_shefit

from employees vartes left outer join employees shefat

on vartes.manager_id=shefat.employee_Id

order by vartes.first_name;

RIGHT OUTER JOIN

select vartes.first_name as emri,

vartes.last_name as mbiemri,

shefat.first_name as emri_shefit,

shefat.last_name as mbiemri_shefit

from employees shefat right outer join employees vartes

on vartes.manager_id=shefat.employee_Id

order by vartes.first_name;

FULL OUTER JOIN

select vartes.first_name as emri,

vartes.last_name as mbiemri,

shefat.first_name as emri_shefit,

shefat.last_name as mbiemri_shefit

from employees shefat full outer join employees vartes

on vartes.manager_id=shefat.employee_Id

order by vartes.first_name;

Ushtrim

Afishoni

Te afishohen departamentet qe nuk kane punonjes.

Zgjidhje

select department_name,first_name,last_name

from departments

left outer join employees

on departments.department_id=employees.department_id;

SELF JOIN

Sintaksa e JOIN me klauzolen ON mundeson qe te bejme JOIN te nje tabele me veten e saj.(Self JOIN ).

Shpesh ka relacione te tabeles me veten e saj per shembull ne tabelen employees kollona Employees.manager_id sherben si celes i jashtem qe shenon tek kollona Employees.Employee_id .

Kollona Manager_Id ruan kodin e manaxherit te nje punonjesi, qe eshte gjithashtu nje punonjes, pra kemi nje relacion te tabeles me vetveten.

Nese duam te bejme JOIN te tabeles me vetveten duhet te krijojme dy "kopje" te tabeles ku sejciles duhet ti caktojme nje ALIAS te ndryshem ne menyre te tille qe te mund ta referojme.

Per shembull nese do te donim te afishonim nje rezultat si ne figuren me poshte:

ku perbri te dhenave te punonjesve te afishonim te dhenat e manaxherit te tij duhet te shkruanim nje komande te ngjashme me:

select

workers.employee_id as kodi_punonjesit,

workers.first_name as emri_punonjesit,

workers.last_name as mbiemri_punonjesit,

managers.employee_id as kodi_shefit,

managers.first_name as emri_shefit,

managers.last_name as mbiemri_shefit

from employees workers join employees managers

on workers.manager_id=managers.employee_Id;

Vini re qe jane krijuar dy "kopje" te tabeles employees njera me emrin (alias) workers dhe tjetra me emrin (alias) managers.

Pas krijimit keto jane trajtuar si te ishin dy tabela te ndryshme ne komanden SELECT.

Non EQUI JOINS

NON EQUI JOINS

Na mundeson qe te bashkojme te dhena nga disa tabele por ku kushti i JOIN nuk eshte kusht barazimi:

Te afishojme per cdo punonjes ne cilen kategori page qendron?

Zgjidhje

select First_name,last_name,grade_level

from employees full outer join job_grades

on employees.salary

between job_grades.lowest_salary

and job_grades.highest_salary;

ose

select First_name,last_name,grade_level

from employees full outer join job_grades

on employees.salary >= job_grades.lowest_salary

and employees.salary<= job_grades.highest_salary;