شروط مقایسه‌ای ALL، ANY و SOME در SQL
شروط مقایسه‌ای ALL، ANY و SOME در SQL و پایگاه داده اوراکل
کاملاً محتمل است که با چندین سال تجربه کار با پایگاه داده اوراکل به دلیل وجود جایگزین‌هایی که بیشتر مورد استفاده قرار دارند، هیچ‌گاه موقعیت استفاده از ALL، ANY و SOME در SQL به وجود نیاید. اما در صورتی که قصد انجام آزمون Oracle Database SQL Expert (1Z0-047) را داشته باشید به دلیل استفاده مکرر در سؤالات باید آشنایی کافی از آن‌ها کسب کنید.
تمام آن چیزی که رأی آزمون نیاز دارید عبارت از شرایط و نحوه کاربرد این عبارات مقایسه‌ای است.این مبحث شامل اطلاعاتی در خصوص نحوه پردازش تغییرات انجام شده توسط بهینه‌ساز می‌باشد، البته این اطلاعات مورد نیاز آزمون نیستند اما به تشریح نحوه انجام فرآیند کمک می‌کند.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————– ———- ———- ———-
۷۳۶۹ SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20
۷۴۹۹ ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300 30
۷۵۲۱ WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500 30
۷۵۶۶ JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20
۷۶۵۴ MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400 30
۷۶۹۸ BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850 30
۷۷۸۲ CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450 10
۷۷۸۸ SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20
۷۸۳۹ KING PRESIDENT 17-NOV-1981 00:00:00 5000 10
۷۸۴۴ TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0 30
۷۸۷۶ ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20
۷۹۰۰ JAMES CLERK 7698 03-DEC-1981 00:00:00 950 30
۷۹۰۲ FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20
۷۹۳۴ MILLER CLERK 7782 23-JAN-1982 00:00:00 1300 10
SQL>
ALL
شرط مقایسه‌ای ALL برای مقایسه یک عنصر با مقادیر یک لیست یا Subquery استفاده می‌شود به طوری که عبارات =، !=، >، <، <=، >= مقدم بر آن استفاده می‌شود و در ادامه لیست یا Subquery آورده می‌شود.
SELECT empno, sal
FROM emp
WHERE sal > ALL (2000, 3000, 4000);
EMPNO SAL
———- ———-
۷۸۳۹ ۵۰۰۰
SQL>
— Transformed to equivalent statement without ALL.
SELECT empno, sal
FROM emp
WHERE sal > 2000 AND sal > 3000 AND sal > 4000;
EMPNO SAL
———- ———-
۷۸۳۹ ۵۰۰۰
SQL>
زمانی که شرط ALL به همراه یک لیست قرار دارد بهینه‌ساز، شرایط اولیه را به تمام عناصر لیست توسط عملگر منطقی AND همانند مثال زیر بسط می‌دهد.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ALL (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 20);
EMPNO SAL
———- ———-
۷۸۳۹ ۵۰۰۰
SQL>
— Transformed to equivalent statement using ANY.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE NOT (e1.sal <= ANY (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 20));
EMPNO SAL
———- ———-
۷۸۳۹ ۵۰۰۰
SQL>
— Transformed to equivalent statement without ANY.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE NOT EXISTS (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 20
AND e1.sal <= e2.sal);
EMPNO SAL
———- ———-
۷۸۳۹ ۵۰۰۰
SQL>
در مواقعی که شرط ALL به همراه Subquery قرار دارد، بهینه‌ساز همانند زیر تبدیل دو مرحله‌ای انجام می‌دهد.
بنا بر این فرض که Subquery ها سطر صفر برنمی‌گرداند، شرایط زیر می‌تواند برای لیست و Subquery ها برقرار باشد:
• (…)x = ALL عنصر مقایسه باید برابر تمام عناصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x != ALL عنصر مقایسه باید برابر هیچکدام از عناصر لیست نباشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x > ALL عنصر مقایسه باید بزرگتر از بزرگترین عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x < ALL عنصر مقایسه باید کوچکتر از کوچکترین عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x >= ALL عنصر مقایسه باید بزرگتر یا مساوی بزرگترین عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x <= ALL عنصر مقایسه باید کوچکتر یا مساوی کوچکترین عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
در صورتی که Subquery هیچ سطری باز نگرداند، شرط برقرار است. به طور مثال در Subquery زیر که هیچ سطری را باز نمی‌گرداند:
در نتیجه تمام عبارت (zero rows) sal > All برقرار است، و تمام سطرها نمایش داده خواهد شد.
SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100;
no rows selected
SQL>
— Place it in the subquery of an ALL and the
— condition “sal > ALL (zero rows)” evaluates to TRUE
— so all rows are displayed.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);
EMPNO SAL
———- ———-
۷۳۶۹ ۸۰۰
۷۹۰۰ ۹۵۰
۷۸۷۶ ۱۱۰۰
۷۵۲۱ ۱۲۵۰
۷۶۵۴ ۱۲۵۰
۷۹۳۴ ۱۳۰۰
۷۸۴۴ ۱۵۰۰
۷۴۹۹ ۱۶۰۰
۷۷۸۲ ۲۴۵۰
۷۶۹۸ ۲۸۵۰
۷۵۶۶ ۲۹۷۵
۷۷۸۸ ۳۰۰۰
۷۹۰۲ ۳۰۰۰
۷۸۳۹ ۵۰۰۰
SQL>
ANY
عبارت مقایسه‌ای ALL برای مقایسه یک عنصر با مقادیر یک لیست یا Subquery استفاده می‌شود به طوری که عبارات =، !=، >، <، <=، >= مقدم بر آن استفاده می‌شود و در ادامه لیست یا Subquery آورده می‌شود.
زمانی که شرطANY به همراه یک لیست قرار دارد بهینه‌ساز، شرایط اولیه را به تمام عناصر لیست توسط عملگر منطقیOR همانند مثال زیر بسط می‌دهد.
SELECT empno, sal
FROM emp
WHERE sal > ANY (2000, 3000, 4000);
EMPNO SAL
———- ———-
۷۵۶۶ ۲۹۷۵
۷۶۹۸ ۲۸۵۰
۷۷۸۲ ۲۴۵۰
۷۷۸۸ ۳۰۰۰
۷۸۳۹ ۵۰۰۰
۷۹۰۲ ۳۰۰۰
SQL>
— Transformed to equivalent statement without ANY.
SELECT empno, sal
FROM emp
WHERE sal > 2000 OR sal > 3000 OR sal > 4000;
EMPNO SAL
———- ———-
۷۵۶۶ ۲۹۷۵
۷۶۹۸ ۲۸۵۰
۷۷۸۲ ۲۴۵۰
۷۷۸۸ ۳۰۰۰
۷۸۳۹ ۵۰۰۰
۷۹۰۲ ۳۰۰۰
SQL>
در مواقعی که شرط ANY به همراه Subquery قرار دارد، بهینه‌ساز همانند زیر تبدیل یک مرحله‌ای انجام می‌دهد.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ANY (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 10);
EMPNO SAL
———- ———-
۷۸۳۹ ۵۰۰۰
۷۹۰۲ ۳۰۰۰
۷۷۸۸ ۳۰۰۰
۷۵۶۶ ۲۹۷۵
۷۶۹۸ ۲۸۵۰
۷۷۸۲ ۲۴۵۰
۷۴۹۹ ۱۶۰۰
۷۸۴۴ ۱۵۰۰
SQL>
SELECT e1.empno, e1.sal
FROM emp e1
WHERE EXISTS (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 10
AND e1.sal > e2.sal);
EMPNO SAL
———- ———-
۷۸۳۹ ۵۰۰۰
۷۹۰۲ ۳۰۰۰
۷۷۸۸ ۳۰۰۰
۷۵۶۶ ۲۹۷۵
۷۶۹۸ ۲۸۵۰
۷۷۸۲ ۲۴۵۰
۷۴۹۹ ۱۶۰۰
۷۸۴۴ ۱۵۰۰
SQL>
بنا بر این فرض که Subquery ها سطر صفر برنمی‌گرداند، شرایط زیر می‌تواند برای لیست و Subquery ها برقرار باشد:
• (…)x = ANY عنصر مقایسه باید برابر یک یا تعداد بیشتری عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x != ANY عنصر مقایسه نباید برابر یک یا تعداد بیشتری عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x > ANY عنصر مقایسه باید بزرگتر از کوچکترین عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x < ANY عنصر مقایسه باید کوچکتر از بزرگترین عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x >= ANY عنصر مقایسه باید بزرگتر یا مساوی کوچکترین عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
• (…)x <= ANY عنصر مقایسه باید کوچکتر یا مساوی بزرگترین عنصر لیست باشد تا عبارت مقایسه‌ای برقرار باشد.
در صورتی که Subquery هیچ سطری باز نگرداند، شرط برقرار نیست. به طور مثال در Subquery زیر که هیچ سطری را باز نمی‌گرداند:
به معنای آن است که کل عبارت zero rows) sal > ANY) برقرار نمی‌باشد، بنابراین هیچ سطری نمایش داده نمی‌شود.
SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100;
no rows selected
SQL>
— Place it in the subquery of an ANY and the
— condition “sal > ANY (zero rows)” evaluates to FALSE
— so no rows are displayed.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ANY (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);
no rows selected
SQL>
SOME
شروط مقایسه‌ای SOME و ANY دقیقاً کار مشابه‌ای را انجام می‌دهد و قابل جایگزینی می‌باشند.
دیدگاه خود را ثبت کنید
تمایل دارید در گفتگوها شرکت کنید؟در گفتگو ها شرکت کنید.