گاهی اوقات لازم است یک دستور SQL طوری نوشته شود که در داخل آن دستورات SQLای دیگری قرار بگیرند و با اجرای دستورات درونی، نتیجه لازم به دستور اصلی برگردد. در این حالت دستور اول Main Query نام دارد و دستورات داخلی را SUBQUERY می نامند.هر SUBQUERY نتیجه لازم را برای Main Query ارسال می کند.

در این متن روش های مختلف استفاده از SUBQUERY در دستورات SQL توضیح داده می شود.

می توان در قسمت عبارت WHERE، FROM و یا HAVING یک دستور SQL، از یک SUBQUERY استفاده نمود. syntax دستور در روش های مختلف SUBQUERY به شکل زیر است که در آن expr operator یک شرط مقایسه ای می باشد.

مثال: می خواهیم نام و تاریخ استخدام کارمندانی که بعد از کارمند Davis استخدام شده اند را نمایش دهیم. بنابراین یک SUBQUERY جهت مشخص نمودن زمان استخدام کارمند Davis و یک Main Query برای نمایش نام سایر کارمندانی که بعد از Davis استخدام شده اند تعریف می شود.

 

برخی قوانین در استفاده از SUBQUERY ها:

1.SUBQUERY باید در داخل پرانتز باشد.

2.به منظور افزایش خوانایی دستور بهتر است SUBQUERY در سمت راست شرط مقایسه ای قرار گیرد.

3.اگر از عملگرهای مقایسه ای که فقط یک سطر را برمی گردانند(Single-Row هستند) استفاده می شود از SUBQUERY از نوع Single-Row استفاده شود و در غیر این صورت از SUBQUERY از نوع Multiple-Row استفاده گردد.

 

انواع روش های SUBQUERY

—Single-Row SUBQUERY

در SUBQUERYهای از نوع Single-Row فقط یک سطر از جدول به Main QUERY برگرداننده می شود. همچنین باید برای آنها از عملگرهای مقایسه ای Single-Row استفاده نمود. این نوع ازعملگرها در جدول زیر مشخص شده اند.

مثال: نمایش نام کارمندانی که job_id آنها برابر با job_id کارمند شماره 142 می باشد.

مثال: نمایش اطلاعات کارمندانی که شغل آنها با شغل کارمند TAYLOR یکسان می باشد ولی حقوق بیشتر از او دریافت می کنند. در این دستور از دو SUBQUERY استفاده شده است.

مثال: نام و حقوق کارمندانی که حقوق آنها برابر با حداقل حقوق دریافتی در شرکت می باشد. در این مثال در داخل SUBQUERY از یک تابع گروهی استفاده شده است.

مثال: نمایش نام دپارتمان هایی که در آنها حداقل حقوق دریافتی کمتر از حداقل حقوق در دپارتمان شماره 30 است. در این مثال در عبارت Having از SUBQUERY استفاده شده است.

مثال: نمایش شغل هایی که میانگین حقوق دریافتی در آنها برابر با حداقل میانگین حقوق دریافتی شغل ها می باشد.

اگر هیچ مقداری برای SUBQUERY وجود نداشته باشد مقدار NULL به MAIN QUERY بازمی گردد.

مثال: هیچ کارمندی با نام ‘Haas’ وجود ندارد بنابراین مقدار NULL به MAIN QUERY برمی گردد.

اگر توسط یک Single-Row SUBQUERY بیشتر از یک سطر برگردانده شود خطای اوراکل دریافت می کنیم. بنابراین دراین حالت باید از Multiple-Row SUBQUERY استفاده شود.

مثال: در این مثال بکارگیری عبارت Group By در SUBQUERY سبب می شود به ازای هر گروه یک سطر مجزا برگردد در حالی که عملگر مقایسه ای در MAIN QUERY علامت ‘=’ می باشد که از نوع Single-Row است و نمی تواند چندین مقدار مختلف را بپذیرد. بنابراین باید از Multiple-Row Query و عملگر IN استفاده نمود.

—Multiple-Row SUBQUERY

این روش از SUBQUERYها بیشتر از یک سطر از جدول را به Main QUERY برمی گردانند و باید برای آنها از عملگرهای مقایسه ای Multiple-Row استفاده نمود. عملگرهای مقایسه ای از نوع Multiple-Row را در جدول زیر می بینید.

قبل از عملگرهای مقایسه ای ANY و ALL یکی از عملگرهای = ، != ،  >  ، < ،  <= و >= استفاده می شود. زمانی که از ANY استفاده می شود باید حداقل یکی از مقدارهای SUBQUERY مطابق شرط مقایسه ای باشد تا مقدار صحیح برگرداننده شود  ولی در عملگر ALL باید تمام مقدارهای SUBQUERY مطابق شرط مقایسه ای باشد تا مقدار صحیح برگردانده شود.

مثال: نمایش مشخصات کارمندانی که حقوق دریافتی آنها برابر با یکی از حقوق های لسیت IN است.

مثال: نمایش اطلاعات کارمندانی که در حوزه IT فعالیت نمی کنند و حقوق دریافتی آنها کمتر از هرکدام از کارمندان حوزه IT کمتر است. در این مثال حقوق کارمندان حوزه IT برابر با 6000 ، 9000 و 4200  است.

زمانی که در یک SUBQUERY یکی از مقدارهای داخلی برابر با مقدار NULL باشد و از عملگر مقایسه ای NOT IN استفاده شود، تمام مقدارهای بازگشتی برای آن SUBQUERY برابر با مقدار NULL خواهد بود.

مثال: نام کارمندانی که مدیر نیستند نمایش یابد. در این مثال به دلیل اینکه در SUBQUERY یکی از رکوردهای ستون manager_id دارای مقدار NULL می باشد و از عملگر NOT IN استفاده شده است، هیچ سطری برنمیگردد.

مثال: در مثال قبلی با استفاده از یک عبارت WHERE مقدارهای NULL نادیده گرفته می شوند و نتایج مورد نظر برگردانده می شود.

مثال: در این مثال با استفاده از عملگر ALL مشخات کارمندانی که حقوق آنها از تمام کارمندان حوزه IT  کمتر است نمایش می یابد. حقوق کارمندان حوزه IT برابر با 6000 ، 9000 و 4200  است.

 

–Multiple Column SUBQUERY

این دسته از SUBQUERYها بیشتر از یک ستون از جدول را به Main QUERY برمی گردانند. همچنین از این روش می توان در قسمت From دستور SELECT نیز استفاده نمود.

مثال: نمایش نام، شماره دپارتمان و حقوق تمام کارمندانی که در هر کدام از دپارتمان ها حداقل حقوق را دریافت می کنند.