Retrieving Data Using Subqueries – AbdulAli Personal Blog

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

————–

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:

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

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 EXISTS–də 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 🙂

November 29, 2015 abdulalialiyev