Exists, In, ANY, ALL In Oracle

Exists: the condition is true when the subquery returns at least one row. NotExists: the condition is true if the subquery does not return any row. In: Same as a subquery returned result set. NotIn: it is not equal to any of the returned results of the subquery. ANY: it is larger than the result returned by the subquery. ANY: It is equal to a value in the returned results of the subquery.

Exists: the condition is true when the subquery returns at least one row. Not Exists: the condition is true if the subquery does Not return any row. In: Same as a subquery returned result set. Not In: Not equal to any of the returned results of the subquery. ANY: it is larger than the result returned by the subquery. = ANY: It is equal to a value in the returned results of the subquery.

Exists: the condition is true when the subquery returns at least one row.

Not Exists: the condition is true if the subquery does Not return any row.

In: It is equal to a value In the returned result set of the subquery.

Not In: it is Not equal to any value In the returned result set of the subquery.

> ANY: it is larger than a value in the returned result of the subquery.

= ANY: It is equal to a value in the returned results of the subquery.

> ALL: greater than ALL values in the returned results of the subquery.

1. query the employee information that is equal to the salary of an employee in department 10.

select empno ,ename,sal from emp where sal in(select sal from emp where deptno=10)

The following statement is the same as the preceding statement.

select empno ,ename,sal from emp where sal=any(select sal from emp where deptno=10)

The effect is as follows:

Note:

= Any () brackets do not report errors even if repeated Values exist. For example:

select empno ,ename,sal from emp where sal=any(2450.00,5000.00,5000.00)

2. Query Information about employees with higher salaries than an employee in department 10.

select empno ,ename,sal from emp where sal >any(select sal from emp where deptno=10)

In the emp table, the minimum wage value is 1300. The following statement is equivalent to the above statement.

select empno ,ename,sal from emp where sal >1300

The effect is as follows:

3. Query Information about employees with higher salaries than all employees in department 10.

select empno ,ename,sal from emp where sal >all(select sal from emp where deptno=10)

The result is as follows (no data is found ):