■副問合せから複数の行が返されるものを複数行副問合せと言う
演算子 |
説明 |
例 |
IN |
値リストの中のいずれかの値と等しい |
SELECT DEPARTMENT_ID, FIRST_NAME,
LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID); |
NOT IN |
値リストの中のいずれかの値とも等しくない |
SELECT DEPARTMENT_ID, FIRST_NAME,
LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID); |
ANY |
副問合せから戻された値のいずれかの値と比較 |
SELECT DEPARTMENT_ID, FIRST_NAME,
LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY < ANY (SELECT MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID); |
ALL |
副問合せから戻された値のすべての値と比較 |
SELECT DEPARTMENT_ID, FIRST_NAME,
LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY >=ALL (SELECT MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
|
|