Salam, Bloguma xoş gəlmisiniz.

Bu yazımda sizlərə ORACLE SQL-in ən önəmli mövzularında olan JOIN-lərdən bəhs edəcəyəm. Məhz bu JOIN-lərə görə ORACLE “Relational Database Management System” adlanır. Mövzumuz bu bölmələrdən ibarət olacaq:

• Equijoins (= operatorunu istifadə edənlər)
• Non-equijoins
• INNER joins
• OUTER joins
• NATURAL joins
• Self-joins
• Cross-joins(Cartesian joins)

EQUIJOINS və Non-EQUIJOINS

• EQUIJOIN-lər cədvəlləri birləşdirdiyi zaman əsasən “=” operatorundan istifadə edir.
• Non-EQUIJOIN – lər cədvəlləri birləşdirdiyi zaman “<, >” müqayisə operatorlarından istifadə edirlər. EQUIJOIN-lərdə məlumatlar hər hansı bir qiymətə bərabər olurdusa burada məlumatlar müəyyən bir aralığa aid olur.

INNER və OUTER joins

• INNER join verilmiş bərabərliyə əsasən cadvəllərdən ancaq bərabərliyi ödəyən məlumatları qaytarır.
• OUTER join verilmis bərabərliyə əsasən cədvəllərdən həm bərabərliyi ödəyən həmdə ödəməyən məlumatları qaytarır.

INNER join

Misallar üzərində izah edəcəyəm.

İsifadə edəcəyimiz cədvəllərin məlumatları ilə birlikdə aşağıda qeyd edirəm.

PORTS cədvəli:

PORT_ID	PORT_NAME
1	Baltimore
2	Charleston
3	Tampa
4	Miami

SHIPS cədəli:

SHIP_ID	SHIP_NAME	HOME_PORT_ID
1	Codd Crystal	1
2	Codd Elegance	3
3	Codd Champion
4	Codd Victorious	3
5	Codd Grandeur	2
6	Codd Prince	2

SELECT                  SHIP_ID,

                                SHIP_NAME,

                                PORT_NAME

FROM                    SHIPS  INNER JOIN PORTS

ON                          HOME_PORT_ID = PORT_ID

ORDER BY            SHIP_ID;

Qeyd: INNER sözü əlavədir yəni yazmasaqda sorğumuz düzgün işləyəcək.

Nəticə:

SHIP_ID     SHIP_NAME               PORT_NAME

———       —————————–    ——————–

1                Codd Crystal                Baltimore

2               Codd Elegance            Tampa

4               Codd Victorious          Tampa

5               Codd Grandeur           Charleston

6               Codd Prince                 Charleston

Qeyd: Nəticədən göründüyü kimi  id-si 3, adı “Codd Champion” olan ship nəticədə çıxmadı çünki INNER join ancaq və ancaq “ON” şərtində verilən bərabərlik ödənildikdə nəticə verir.

Join-lərdə də digər sql sorğular kimi WHERE ifadəsini istifadə edə bilərik, tək qayda “ON” ifadəsindən sonra işlədilməlidir.

Məs:

SELECT                 SHIP_ID,

                                SHIP_NAME,

                                PORT_NAME

FROM                    SHIPS INNER JOIN PORTS

ON                         HOME_PORT_ID = PORT_ID

WHERE                 PORT_NAME = ‘Charleston’

ORDER BY            SHIP_ID;

Nəticə:

SHIP_ID     SHIP_NAME               PORT_NAME

———       —————————–    ——————–

5               Codd Grandeur          Charleston

6               Codd Prince                Charleston 

Qeyd: INNER join –lər yazmağın bir digər üsulu isə ORACLE-ın əvvəlki versiyalarında istifadə etdiyi və hal hazırda da işlək olan üsuldur:

SELECT                  S.SHIP_ID,

                                 S.SHIP_NAME,

                                P.PORT_NAME

FROM                    SHIPS S, PORTS P

WHERE                 S.HOME_PORT_ID = P.PORT_ID

ORDER BY            S.SHIP_ID;

————————————

SELECT                  S.SHIP_ID,

                                 S.SHIP_NAME,

                                 P.PORT_NAME

FROM                    SHIPS S, PORTS P

WHERE                 S.HOME_PORT_ID = P.PORT_ID

AND                       PORT_NAME = ‘Charleston’

ORDER BY            S.SHIP_ID;         

Qeyd: Son 2 sorğuda istifadə etdiyimiz cədvəllərin yanına birdə “S” və “P” yazdıq bunun yazmaq məqsədimiz cədvələ alians verərək hər dəfə cədvəlin tam adını yazmaqdan canımızı qurtarmaq idi,  bir növ cədvələ sorğu daxilində yeni ad təyin etmiş olduq. Alians-lar həm cədvəl adlarına həm sütun adlarına sorğu daxilində alternativ adlar verir və çox geniş istifadə olunur.

OUTER join

OUTER join-lər INNER joinlərdə olduğu kimi “ON” ifadəsindəki bərabərliyi ödəyən nəticələri bizə qaytarır ancaq əlavə olaraq bərabərliyi ödəməyən məlumatlarıda bizə qaytarır. 3 növü var:

• LEFT OUTER JOIN(LEFT JOIN)
• RIGHT OUTER JOIN(RIGHT JOIN)
• FULL OUTER JOIN(FULL JOIN)

Qeyd: OUTER sözü əlavədir, sorğularda istifadə etsək də etməsək də nəticəyə təsir etmir.

LEFT OUTER JOIN(LEFT JOIN)

LEFT join sintaksisində LEFT-dən solda yazılmış cədvəlin bütün sətirlərini və o sətirlərə sağdakı cədvəldən uyğun gələn sətirləri qaytarır.

Yenə SHIPS və PORTS cədvəllərinə baxaq və bu cədvəllər üzərində LEFT join yaradaq.

SELECT                  SHIP_ID,

                                SHIP_NAME,

                                PORT_NAME

FROM                    SHIPS LEFT OUTER JOIN PORTS

ON                         HOME_PORT_ID = PORT_ID

ORDER BY            SHIP_ID;

Nəticə:

SHIP_ID     SHIP_NAME               PORT_NAME

———       —————————–    ——————–

1                Codd Crystal                Baltimore

2                Codd Elegance           Tampa

3                Codd Champion

4                Codd Victorious          Tampa

5                Codd Grandeur           Charleston

6                Codd Prince                 Charleston

Nəticədən də gördüyümüz kimi SHIPS cədvəlinin bütün sətirləri saxlanıldı və onlara uyğun nəticələr qaytarıldı, uyğun olmayanlar isə NULL qaytarılacaq. Gəlin biz bu NULL ifadələri əvvəl öyrəndiyimiz bir funksiya ilə dəyişək və nəticəni daha anlaşıqlı edək.

SELECT                  SHIP_ID,

                               SHIP_NAME,

                               NVL(PORT_NAME, ‘-‘)

FROM                   SHIPS LEFT OUTER JOIN PORTS

ON                         HOME_PORT_ID = PORT_ID

ORDER BY            SHIP_ID;

Nəticə:

SHIP_ID     SHIP_NAME               PORT_NAME

———       —————————–    ——————–

1                Codd Crystal                Baltimore

2                Codd Elegance           Tampa

3                Codd Champion          –

4                Codd Victorious          Tampa

5                Codd Grandeur           Charleston

6                Codd Prince                 Charleston

RIGHT OUTER JOIN(RIGHT JOIN)

RIGHT join sintaksisində RIGHT-dən sağda yazılmış cədvəlin bütün sətirlərini və o sətirlərə soldakı cədvəldən uyğun gələn sətirləri qaytarır.

Yenə SHIPS və PORTS cədvəllərinə baxaq və bu cədvəllər üzərində RIGHT join yaradaq.

SELECT                  SHIP_ID,

                                SHIP_NAME,

                                PORT_NAME

FROM                    SHIPS RIGHT OUTER JOIN PORTS

ON                         HOME_PORT_ID = PORT_ID

ORDER BY            SHIP_ID;

Nəticə:

SHIP_ID     SHIP_NAME               PORT_NAME

———       —————————–    ——————–

1                Codd Crystal                  Baltimore

2               Codd Elegance              Tampa

4               Codd Victorious             Tampa

5               Codd Grandeur              Charleston

6               Codd Prince                   Charleston

                                                           Miami

SELECT                  NVL(SHIP_ID, ‘-‘),

                                 NVL(SHIP_NAME, ‘-‘),

                                 PORT_NAME

FROM                    SHIPS RIGHT OUTER JOIN PORTS

ON                         HOME_PORT_ID = PORT_ID

ORDER BY            SHIP_ID;

Nəticə:

SHIP_ID     SHIP_NAME               PORT_NAME

———       —————————–    ——————–

1                Codd Crystal                  Baltimore

2               Codd Elegance              Tampa

4               Codd Victorious             Tampa

5               Codd Grandeur              Charleston

6               Codd Prince                    Charleston

–                              –                          Miami

FULL OUTER JOIN(FULL JOIN)

FULL OUTER JOIN  =  RIGHT OUTER JOIN + LEFT OUTER JOIN.

SELECT                  SHIP_ID,

                                SHIP_NAME,

                                PORT_NAME

FROM                    SHIPS FULL OUTER JOIN PORTS

ON                         HOME_PORT_ID = PORT_ID

ORDER BY            SHIP_ID;

Nəticə:

SHIP_ID     SHIP_NAME               PORT_NAME

———       —————————–    ——————–

1               Codd Crystal                  Baltimore

2              Codd Elegance              Tampa

3              Codd Champion

4              Codd Victorious            Tampa

5              Codd Grandeur            Charleston

6              Codd Prince                  Charleston

                                                         Miami

SELECT                  NVL(SHIP_ID, ‘–‘),

                                 NVL(SHIP_NAME, ‘–‘),

                                 NVL(PORT_NAME, ‘–‘)

FROM                    SHIPS FULL OUTER JOIN PORTS

ON                         HOME_PORT_ID = PORT_ID

ORDER BY            SHIP_ID;

Nəticə:

SHIP_ID     SHIP_NAME               PORT_NAME

———       —————————–    ——————–

1               Codd Crystal                  Baltimore

2              Codd Elegance              Tampa

3              Codd Champion             –

4              Codd Victorious            Tampa

5              Codd Grandeur            Charleston

6              Codd Prince                  Charleston

 –              –                                      Miami

Qeyd: Bir digər OUTER join yaratmaq üsulu isə bu şəkildədir. (+) sağda olarsa LEFT OUTER JOIN, solda olarsa RIGHT OUTER JOIN olaraq işləyir. Oracle bu şəkildə yazılışı tövsiyə etmir ancaq hələdə işlək bir sintaksis olaraq istifadə olunur bu üsul.

SELECT                  SHIP_ID,

                                 SHIP_NAME,

                                 PORT_NAME

FROM                    SHIPS, PORTS

WHERE                 HOME_PORT_ID = PORT_ID(+)

ORDER BY            SHIP_ID;

NATURAL join

İndiyə kimi baxdığımız join-lərdə primary key(PK) və foreign key(FK) –lərin aid olduğu sütunlar fərqli adlarda(HOME_PORT_ID, PORT_ID) idi, birdə bu constraintlərin aid olduğu sütunlar eyni adda olduğu zaman istifadə etidiyimiz join növü var hansıki NATURAL join adlanır.

Məsələn EMPLOYEES və ADRESSES cədvəllərinin strukturuna baxdıqda PK və FK –nın eyni ada aid olduğunu görürük.

Bu cədvəlləri join etməyimiz üçün belə yazmağımız kifayətdir:

SELECT                  EMPLOYEE_ID,

                                 LAST_NAME,

                                 STREET_ADDRESS

FROM                     EMPLOYEES NATURAL JOIN ADDRESSES;

Qeyd: NATURAL join INNER join-dir yəni ancaq primary key-in qiymətinin foreign key qiymətinə bərabər olduğu zaman məlumat qaytarır. Həm OUTER həm INNER ola biləcək join-lər isə USING işlətməklə yazılır, aşağıda bundan bəhs edəcəyəm.

USING

USING eyni ilə NATURAL join kimi primary key və foreign key constraintlərinin sütun adları eyni olduğu zaman istifadə olunur. NATURAL joindən fərqi isə həm OUTER həm INNER join kimi istifadə oluna bilməsidir.

Yenə EMPLOYEES və ADDRESSES cədvəllərin istifadə edərək bu dəfə USING vasitəsilə join yaradaq.

SELECT                  EMPLOYEE_ID,

                                LAST_NAME,

                                STREET_ADDRESS

FROM                    EMPLOYEES LEFT JOIN ADDRESSES

USING                  (EMPLOYEE_ID);

MUTLITABLE join

İndiyə kimi ancaq 2 cədvəlin birləşdirilməsinə baxdıq ancaq 2dən çox 3,4,5 …n cədvəlidə birləşdirmək mümkündür.

Məsələn  strukturları bu şəkildəki kimi olan 3 cədvəli birləşdirilməsinə baxaq.

SELECT                  P.PORT_NAME,

                                S.SHIP_NAME,

                                SC.ROOM_NUMBER

FROM                    PORTS P JOIN SHIPS S

ON                         P.PORT_ID = S.HOME_PORT_ID

JOIN                      SHIP_CABINS SC

ON                         S.SHIP_ID = SC.SHIP_ID;

Non-EQUIJOINS

Yazımın bu hissəsinə kimi ancaq “equijoin”-lərdən istifadə etdik yəni cədvəllərimizi hər hansı bir bərabərlik nəticəsində birləşdirib məlumat əldə etdik.

Non-equijoin-lər bizə bərabər(=) işarəsindən istifadə etmədən, onun yerinə müqayisə operatorlarında istifadə edərdək cədvəllərimizi birləşdirməyimizə yardım edir.

Bir misal üzərində izah edəcəyəm.

2 cədvəlimiz olsun. 1-də tələbələrin aldığı qiymətlər saxlayaq və adı “SCORES” olsun, 2-də hansı qiymət aralığının nəyi bildirdiyini saxlayaq və adı “GRADING” olsun. Bu şəkildə:

 

SCORE_ID	TEST_SCORE
1	95
2	55
3	83

 

GRADING_ID	GRADE	SCORE_MIN	SCORE_MAX
1	A	90	100
2	B	80	89
3	C	70	79
4	D	60	69
5	E	50	59

SELECT                  S.SCORE_ID,

                                 S.TEST_SCORE,

                                G.GRADE

FROM                    SCORES S JOIN GRADING G

ON                         S.TEST_SCORE BETWEEN G.SCORE_MIN AND G.SCORE_MAX;   

Nəticə:

SCORE_ID  TEST_SCORE    GRADE

————       —————-       ———

1                   95                     A

3                   83                     B

2                   55                     E

SELF-JOIN

Self join bir cədvəlin yenidən özünə join olunmasıdır. Self join-lər INNER, OUTER, EQUIJOIN, həmçinin NON-EQUIJOIN ola bilərlər.

Məsələn “POSITIONS” cədvəlimizi yenidən özünə birləşdirək.

 

POSITION_ID	POSITION	REPORTS_TO
1	Captain
2	Director	1
3	Manager	2
4	Crew Chief	2
5	Crew	4

Cədvəlimiz özünə birləşdirmək üçün foreign key constraint-inin olması tövsiyə olunur, ilk öncə foreign key –imizi yaradaq.

ALTER TABLE      POSITIONS

ADD                        CONSTRAINT FK_PO FOREIGN KEY (REPORTS_TO)

REFERENCES       POSITIONS (POSITION_ID);

İndi self join-imiz rahat bir şəkildə yaza bilərik.

SELECT                  A.POSITION_ID,

                                A.POSITION,

                                B.POSITION BOSS

FROM                    POSITIONS A LEFT OUTER JOIN POSITIONS B (eyni cədvələ fərqli 2 alians veririk)

ON                         A.REPORTS_TO = B.POSITION_ID

ORDER BY            A.POSITION_ID; 

POSITION_ID       POSITION              BOSS

——————–     ———————-   ——————–

1                            Captain

2                            Director                 Captain

3                            Manager               Director

4                            Crew Chief           Director

5                            Crew Crew           Chief

CROSS join

CROSS join həmçinin “CARTESİAN product” –da adlanır. Yazdığımız SELECT sorğularda heç bir join şərti istifadə etmədən 1dən çox cədvəldən məlumat aldığımız zaman baş verir cross join.

Cədvəllərimizin birində n digərində m sətir məlumat varsa biz cross join sonrası n*m sətir məlumat əldə edəcəyik.

Məsələn VENDORS və SUBSCRIBERS cədvəllərini cross join edək.

VENDORS

VENDOR_ID	VENDOR_NAME
1	Acme Steaks
2	Acme Poker Chips

SUBSCRIBERS

SUBSCRIBER_ID	LASTNAME
1	KLINE
2	bryant
3	McLean

1)

SELECT                  *

FROM                   VENDORS, ONLINE_SUBSCRIBERS;

2)

SELECT                  *

FROM                   VENDORS CROSS JOIN ONLINE_SUBSCRIBERS;

1 və 2-ci sorğular bir birinə ekvivalentdir.

Nəticə:

VENDOR_ID        VENDOR_NAME       SUBSCRIBER_ID     LASTNAME

——————      ————————-       ———————-        ————–

1                             Acme Steaks                1                               KLINE

1                             Acme Steaks                2                               bryant

1                             Acme Steaks                3                               McLean

2                             Acme Poker Chips      1                                KLINE

2                             Acme Poker Chips      2                                bryant

2                             Acme Poker Chips      3                                McLean

Qeyd: Oracle SQL – də cross join kimi bir xüsusiyyət olsa da bu join növünü tətbiqlərinizdə istifadə etmək heç də faydalı deyil və eyni zamanda tövsiyə olunmur, istifadəsinin yararlı olduğunu düşünən və ya sıx istifadə edən varsa şərh yazsın görək həqiqətən yararlı ola bilirmi cross join 🙂

 

Bu yazımda da bu qədər, ümid edirəm sizə faydalı bilgilər verə bildim, fikirlərinizi eyni zamanda tənqidlərinizi şərh yazaraq bildirin 🙂