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

 emri mbiemri emri_shefit Mbiemri_Shefit
    

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

 Department_name First_name Last_Name
   

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?

 First_name Last_name jog_grade
   

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;