IN Condition
In PL/SQL, the IN condition is a logical condition used in queries to specify a range of values. It is commonly used in the WHERE clause of a SELECT, UPDATE, DELETE, or MERGE statement to filter rows based on a specific list of values. Using the IN condition can make queries more concise and readable, especially when dealing with multiple values or subqueries.
Syntax:
query...
EXISTS (subquery/value_list)
query...
Example 1
The following query will output all the employees which have an odd employee_id:
Query:
SELECT * FROM employees
WHERE employee_id IN
(
SELECT employee_id FROM employees
WHERE MOD(employee_id,2)=1
);
Output:
Output
Example 2
Let’s fetch the information about all the employees whose name starts with A.
Query:
SELECT * FROM employees
WHERE employee_id IN
(
SELECT employee_id FROM employees
WHERE employee_name LIKE 'A%'
);
Output:
Output
Explanation: In the above query we have fetched the information related all the employees whose name starts with A.
Example 3:
The following query finds out all the employee which have a manager assigned to them.
Query:
SELECT * FROM employees
WHERE employee_id IN
(
SELECT employee_id FROM employees
WHERE manager_id IS NOT NULL
);
Output:
Output
Explanation: In the above query we have fetched the information related all the employee which have a manager assigned to them.
Difference between EXISTS and IN in PL/SQL
PL/SQL is a procedural language designed to enable developers to combine the power of procedural language with Oracle SQL. Oracle develops and serves as one of the three key programming languages embedded in the Oracle database, alongside SQL and Java. PL/SQL includes procedural language elements such as conditions and loops and can handle exceptions (run-time errors). It also allows the declaration of constants and variables, procedures, functions, packages, types and variables of those types, and triggers. In this article, we are going to see the differences between EXISTS and IN in PL/SQL.
Contact Us