Salam, Bloguma xoş gəlmisiniz

Bugün sizlərə alt sorğulardan(subquery) yazacağam. Hər hansı sql sorğusu içərisindəki bir digər sql sorğu istifadə olunarsa daxildəki sorğuya alt sorğu deyəcəyik qısaca SELECT daxilində 2ci və ya nci bir SELECT alt sorğu adlanır.

Yazının davamında  bəzən alt sorğular, bəzən suquery deyə müraciət edəcəyəm çünki bəzi yerlərdə “alt sorğu” işlətmək biraz qəribə səslənir, ümid edirəm anlayışla qarşılayarsınız bu qərarımı 🙂

Alt sorğular təkcə SELECT ifadələrdə yox həmçinin INSERT, UPDATE, DELETE kimi DML ifadələrdə və eyni zamanda CREATE TABLE, CREATE VIEW kimi DDL ifadələrinin daxilində də istifadə olunur.

Bir çox subquery-lər müstəqil olaraq ana(parent) sorğudan ayırdıqda işləyirlər ancaq bəziləri ana sorğu ilə bağlı olurlar ayrıca işləmir,  bunlara “correlated” subquery deyəcəyik.

ALT SORĞULAR-ın TİPLƏRİ

  • SINGLE ROW SUBQUERIES (tək sətirli alt sorğular)

Ana sorğuya 1 sətirlik məlumat qaytarır, sütun sayı 1 və 1-dən çox ola bilər ancaq sətir sayı ancaq 1-ə bərabərdir.

Məs: “EMP” cədvəlimizdən adı “GUTI” soyadı “HAZ” olan işçinin işlədiyi gəmidəki işçilərin siyahısını çıxaraq. İlkin vəziyətdə gəlin bu sorğunu subquery işlətmədən həll edək, bu zaman 2 fərqli sorğu yazmalıyıq.

SELECT         SHIP_ID

FROM            EMP

WHERE         LAST_NAME = ‘HAZ’ AND FIRST_NAME = ‘GUTI’;

SHIP_ID

————–

1

Gəminin ID-sini bildikdən sonra 2ci sorğumuzu yazaq.

SELECT          EMPLOYEE_ID,

                        LAST_NAME,

                        FIRST_NAME

FROM            EMP

WHERE         SHIP_ID = 1

AND NOT     (LAST_NAME = ‘HAZ’ AND FIRST_NAME = ‘GUTI’);

EMPLOYEE_ID         LAST_NAME                   FIRST_NAME

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

1                                Hoddlestein                     Howard

7                                Worthington                    Buffy

Verilən tapşırığı subquery istifadə etmədən həll etdik, indi isə subquery istifadə edək və 1 sorğuda nəticəyə baxaq.

SELECT          EMPLOYEE_ID,

                        LAST_NAME,

                        FIRST_NAME

FROM            EMP

WHERE         SHIP_ID = (SELECT           SHIP_ID

                                          FROM              EMPLOYEES

                                          WHERE           LAST_NAME = ‘HAZ’

                                          AND                 FIRST_NAME = ‘GUTI’)

AND NOT     (LAST_NAME = ‘HAZ’ AND FIRST_NAME = ‘GUTI‘);

EMPLOYEE_ID         LAST_NAME                   FIRST_NAME

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

1                                Hoddlestein                     Howard

7                                Worthington                    Buffy

Qeyd: Single row subquery-lərdə hər zaman alt sorğunun ana sorğuya yalnız və yalnız 1 sətir qaytarmasına diqqət etmək lazımdır, əks təqdirdə aşağıdakı kimi xəta alacaqsınız.

SELECT          EMPLOYEE_ID,

                        LAST_NAME,

                        FIRST_NAME

FROM            EMPLOYEES

WHERE         SHIP_ID = (SELECT           SHIP_ID

                                         FROM              EMPLOYEES

                                        WHERE            LAST_NAME = ‘HAZ’)

AND NOT (LAST_NAME = ‘HAZ’);

Error starting at line 1 in command:

Error report:

SQL Error: ORA-01427: single-row subquery returns more than one row

1427.00000 – “single-row subquery returns more than one row

 

  • MULTIPLE ROW SUBQUERIES (çox sətirli alt sorğular)

Ana sorğuya 1 və 1-dən çox sətir məlumat qaytarır, hər zaman çox sətir qaytaracaq deyə bir qayda yoxdur ancaq adətən bir neçə sətir qaytarır. Bu tip subquery-lər əsasən ana sorğu ilə müqayisə operatorları vasitəsilə əlaqələndirilir.

Məs: Sonuncu yazmış olduğumuz sorğunun üzərində kiçik bir dəyişiklik edib baş verən xətanı aradan qaldıraq həm də eyni zamanda çox sətirli alt sorğularada misal vermiş olarıq.

SELECT          SHIP_ID,

                        LAST_NAME,

                        FIRST_NAME

FROM            EMP

WHERE         SHIP_ID IN (SELECT         SHIP_ID

                                            FROM             EMPLOYEES

                                            WHERE          LAST_NAME = ‘HAZ’)

ORDER BY    SHIP_ID, LAST_NAME;

Nəticə:

SHIP_ID                     LAST_NAME                    FIRST_NAME

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

1                                 HODDLESTEIN                   HOWARD

1                                 HAZ                                      GUTI

1                                 WORTHINGTON                BUFFY

3                                 LINDON                              ALICE

3                                 HAZ                                      JOE

Multi-row subquery-lər bir neçə müqayisə operatorları ilə istifadə olunurlar.Bu operatorlar IN, ANY, SOME, ALL operatorlarıdır.

 

OPERATOR AÇIQLAMA
IN Axtarılan dəyəri öz parametrləri arasında axtarır. Axtarılan dəyər parametrlərindən hər hansı birinə bərabər olduqda alt sorğu TRUE qaytarır, alt sorğu məlumat qaytarmadıqda FALSE qaytarır.
NOT IN və digər sadalanan operatorlarla işlənir və normal operator nə dəyər qaytarırsa onun əksini ana sorğuya qaytarır.(TRUE -> FALSE)
ANY Single-row subquery-lərda istifadə olunan müqayisə operatorları ilə birlikdə istifadə olunur( = ANY , > ANY , < ANY kimi… ). Alt sorğuda məlumatdan hər hansı biri şərtə uyğun gələrsə TRUE, heç bir nəticə tapılmazsa FALSE qaytarır.
SOME ANY ilə tamamilə eyni işi görür
ALL Single-row subquery-lərda istifadə olunan müqayisə operatorları ilə birlikdə istifadə olunur( = ANY , > ANY , < ANY kimi… ). Alt sorğuda məlumatlardan hər biri şərtə uyğun gələrsə TRUE, heç bir nəticə tapılmazsa yenə TRUE qaytarır.

IN operatoruna baxmışdıq indi isə digər operatora aid sorğular yazıb multi-row subquery-ləri sonlandıraq. Bunun üçün “emp” cədvəlini istifadə edəcəyik, aşağıdakı kimi:

table_emp

ALL

SELECT                  EMPNO, SAL

FROM                     EMP   

WHERE                  SAL > ALL (2000, 3000, 4000); (1.1)

SELECT                  EMPNO, SAL

FROM                     EMP

WHERE                  SAL > 2000 AND SAL > 3000 AND SAL > 4000;(1.2)

EMPNO        SAL

———-      ———-

7839             5000

1.1 və 1.2 sorğuları bir-birinə ekvivalentdir .

ANY

SELECT                  EMPNO, SAL

FROM                     EMP

WHERE                 SAL > ANY (2000, 3000, 4000);(2.1)

SELECT                 EMPNO, SAL

FROM                    EMP

WHERE                 SAL > 2000 OR SAL > 3000 OR SAL > 4000;(2.2)

EMPNO      SAL

————–  ———-

7566       2975

7698       2850

7782       2450

7788       3000

7839       5000

7902       3000

2.1 və 2.2 sorğuları bir-birinə ekvivalentdir . (SOME eynilə ANY kimi işləyir)

 

  • MULTIPLE COLUMN SUBQUERIES(çox sütunlu alt sorğular)

Ana sorğuya 1-dən çox sütun məlumat qaytarır, bu məlumatlar 1 və ya 1-dən çox sətirlik ola bilər.

SELECT          EMPLOYEE_ID

FROM             EMPLOYEES

WHERE         (FIRST_NAME, LAST_NAME) IN

(SELECT         FIRST_NAME, LAST_NAME

FROM            CRUISE_CUSTOMERS)

AND               SHIP_ID = 1;

Qeyd: WHERE şərtində yazılmış sütunların tipləri alt sorğuda olan müvafiq sütunların tipləri ilə oxşar olmalıdır.

SELECT          INVOICE_ID

FROM             INVOICES

WHERE         (INVOICE_DATE, TOTAL_PRICE) =

(SELECT       START_DATE,

                      SALARY

FROM          PAY_HISTORY

WHERE       PAY_HISTORY_ID = 4);

pay_history_invoices

  • SCALAR SUBQUERIES(skalyar alt sorğular)

Skaylar alt sorğular ana sorğuya yalnız 1 sütun və 1 sətir qaytarır.

SELECT          VENDOR_NAME,

                         (SELECT TERMS_OF_DISCOUNT FROM INVOICES WHERE INVOICE_ID = 1) AS DISCOUNT

FROM             VENDORS

ORDER BY     VENDOR_NAME;

VENDOR_NAME         DISCOUNT

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

Acme Poker Chips       2 pct on 30

Acme Steaks                 2 pct on 30

INSERT INTO EMPLOYEES ( EMPLOYEE_ID, SHIP_ID)

VALUES ( SEQ_EMPLOYEE_ID.NEXTVAL, (SELECT SHIP_ID FROM SHIPS WHERE SHIP_NAME = ‘Codd Champion’));

Skalyar alt sorğuları işlədə bilməyəcəyimiz bir neçə ifadələr var:

  • CHECK constraint-də
  • GROUP BY – da
  • HAVING – də
  • Funksiya əsaslı INDEX-lərdə
  • Sütuna verdiymiz DEFAULT dəyərində
  • DML ifadələrinin RETURNING hissəsində
  • CASE-in WHEN şərtində
  • START WITH, CONNECT BY ifadələrində

 

  • CORRELATED SUBQUERIES(əlaqəli alt sorğular)

Ana sorğu ilə alt sorğunun qarşılıqlı bir-birinə müraciət etməsidir.

Belə bir cədvəlimiz olsun və içinə də bu məlumatları daxil edək.

CREATE TABLE SHIP_CABINS(

                SHIP_CABIN_ID NUMBER,

                ROOM_NUMBER NUMBER,

                ROOM_STYLE VARCHAR2(20),

                ROOM_TYPE VARCHAR2(20),

                WINDOW VARCHAR2(20),

                GUESTS NUMBER,

                SQ_FT NUMBER

);

INSERT INTO SHIP_CABINS VALUES(1,102,’SUITE’, ‘STANDART’, ‘OCEAN’,4,533);

INSERT INTO SHIP_CABINS VALUES(2,103,’STATEROOM’, ‘STANDART’, ‘OCEAN’,2,160);

INSERT INTO SHIP_CABINS VALUES(3,104,’SUITE’, ‘STANDART’, ‘NONE’,4,533);

INSERT INTO SHIP_CABINS VALUES(4,105,’STATEROOM’, ‘STANDART’, ‘OCEAN’,3,205);

INSERT INTO SHIP_CABINS VALUES(5,106,’SUITE’, ‘STANDART’, ‘NONE’,6,586);

INSERT INTO SHIP_CABINS VALUES(6,107,’SUITE’, ‘ROYAL’, ‘OCEAN’,5,1524);

INSERT INTO SHIP_CABINS VALUES(7,108,’STATEROOM’, ‘LARGE’, ‘NONE’,2,211);

INSERT INTO SHIP_CABINS VALUES(8,109,’STATEROOM’, ‘STANDART’, ‘NONE’,2,180);

INSERT INTO SHIP_CABINS VALUES(9,110,’STATEROOM’, ‘LARGE’, ‘NONE’,2,225);

INSERT INTO SHIP_CABINS VALUES(10,702,’SUITE’, ‘PRESIDENTIAL’, ‘NONE’,5,1142);

INSERT INTO SHIP_CABINS VALUES(11,703,’SUITE’, ‘ROYAL’, ‘OCEAN’,5,1745);

INSERT INTO SHIP_CABINS VALUES(12,704,’SUITE’, ‘SKYLOFT’, ‘OCEAN’,8,722);

COMMIT;

Indi isə belə bir tapşırığı yerinə yetirən sorğu yazaq. Deməli belə, eyni stilə sahib otaqların sahələrinin, həmin stilə sahib otaqların ortalama sahəsindən böyük olanların ID, ROOM_STYLE, ROOM_NUMBER,SQ_FT kimi dəyərlərini ekrana çıxaraq.

SELECT                  A.SHIP_CABIN_ID,

                                A.ROOM_STYLE,

                                A.ROOM_NUMBER,

                                A.SQ_FT

FROM                   SHIP_CABINS A

WHERE                 A.SQ_FT > (SELECT          AVG(SQ_FT)

                                                   FROM             SHIP_CABINS

                                                   WHERE          ROOM_STYLE = A.ROOM_STYLE)

ORDER BY            A.ROOM_NUMBER;

Nəticə:

SHIP_CABIN_ID          ROOM_STYLE        ROOM_NUMBER  SQ_FT

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

4                                  Stateroom               105                               205

6                                  Suite                         107                             1524

7                                  Stateroom               108                               211

9                                  Stateroom               110                               225

10                                Suite                         702                            1142

11                                 Suite                         703                            1745

EXISTS və NOT EXISTS

EXISTS ifadəsi sətirin alt sorğuda var olub olmadığını yoxlayır. Varsa TRUE yoxsa FALSE qaytarır. NOT EXISTSdə eyni işi görür ancaq sətir alt sorğuda varsa FALSE yoxsa TRUE qaytarır.

SELECT                 PORT_ID, PORT_NAME

FROM                   PORTS P1

WHERE                EXISTS (SELECT                  *

FROM                   SHIPS S1

WHERE                P1.PORT_ID = S1.HOME_PORT_ID);

Qeyd: Bir cədvəlin sətirlərini digər bir cədvəl ilə EXISTS vasitəsilə müqayisə edən SELECT sorğulara “semijoin” deyilir.

WITH   

Yazdığımız alt sorğulara WITH vasitəsilə ad verə bilərik.

WITH

        PORT_BOOKINGS AS (

                  SELECT                 P.PORT_ID,

                                                  P.PORT_NAME,

                                                 COUNT(S.SHIP_ID) CT

                  FROM                    PORTS P, SHIPS S

                 WHERE                  P.PORT_ID = S.HOME_PORT_ID

                GROUP BY             P.PORT_ID, P.PORT_NAME

),

DENSEST_PORT AS (

              SELECT                  MAX MAX_CT

              FROM                     PORT_BOOKINGS

)

SELECT                 PORT_NAME

FROM                    PORT_BOOKINGS

WHERE                 CT = (SELECT MAX_CT FROM DENSEST_PORT);

 

Bu yazımdan da bu qədər, ümid edirəm sizə faydalı ola biləcək bilgilər verə bildim, şərhlərinizi və ya tənqidlərinizi əksik etməyin dostlar 🙂

641 Ümumi oxunma 2 Bugün oxunma