IN Operator
You can define several values in a WHERE clause by using the IN operator. Multiple OR conditions are condensed into the IN operator.
Employee Table
| emp_id | emp_name | salary | dept_id | manager_id |
|---|---|---|---|---|
| E1 | Rahul | 15000 | D1 | M1 |
| E2 | Manoj | 15000 | D1 | M1 |
| E3 | James | 55000 | D2 | M2 |
| E4 | Michael | 25000 | D2 | M2 |
| E5 | Ali | 20000 | D10 | M3 |
| E6 | Robin | 35000 | D10 | M3 |
Example
The SQL statement that follows chooses all employees whose department ids are “D1” or “D10.”
SELECT * FROM Employee
WHERE dept_id IN ('D1','D10');
Output
| emp_id | emp_name | salary | dept_id | manager_id |
|---|---|---|---|---|
| E1 | Rahul | 15000 | D1 | M1 |
| E2 | Manoj | 15000 | D1 | M1 |
| E5 | Ali | 20000 | D10 | M3 |
| E6 | Robin | 35000 | D10 | M3 |
EXISTS Operator
Any record in a subquery can be checked for existence using the EXISTS operator. If the subquery returns one or more records, the EXISTS operator returns TRUE.
Department Table
| dept_id | dept_name |
|---|---|
| D1 | IT |
| D2 | HR |
| D3 | Finance |
| D4 | Admin |
Example
The following SQL statement identifies the departments that have employees and returns TRUE.
SELECT dept_name
FROM Department
WHERE EXISTS (SELECT dept_id FROM Employee
WHERE Employee.dept_id = Department.dept_id )
Output
| dept_name |
|---|
| IT |
| HR |
Code Runner
Copy the below queries in the code runner to see the result
SELECT * FROM Employee
WHERE dept_id IN ('D1','D10');
SELECT dept_name
FROM Department
WHERE EXISTS (SELECT dept_id FROM Employee
WHERE Employee.dept_id = Department.dept_id )