CODEWITHSUNDEEP
sqloracle-databaseexists
sangmin park
sangmin park

Reputation: 567

ORACLE EXIST (Subquery)

From what I learned, SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row.

Then why do these code give different results?

/* #1 */
SELECT * FROM employees
WHERE EXISTS(
SELECT * FROM employees
WHERE employees.department_id= 20);

/* #2 */
SELECT * FROM employees
WHERE EXISTS(
SELECT * FROM departments
WHERE employees.department_id= 20);

I would imagine if any of the subqueries return any rows they evaluate TRUE. Therefore, the outer query will return all rows from the employee tables.

However, only the #1 code returns all rows from the employee table. #2 returns ros that have department_id=20.

Upvotes: 1

Views: 2571

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is your first query:

SELECT e.*
FROM employees e
WHERE EXISTS (SELECT 1
              FROM employees e2
              WHERE e2.department_id = 20
             );

In this query, the inner WHERE is referring to the inner table. So one of two things can happen:

  • employees has an employee in that department. In that case, all employees are returned in the outer query.
  • employees has no employees in that department. In that case, no employees are returned in the outer query.

This is all-or-thing.

The second query is:

SELECT e.*
FROM employees e
WHERE EXISTS (SELECT 1
              FROM departments d
              WHERE e.department_id = 20
             );

The inner reference is to the outer query. For each row in employees, the query goes and looks to see if the subquery returns any rows. If it does, then the row is returned.

The subquery will return either all rows from departments (if the employee in the outer query has department_id = 20) or no rows. So, this is equivalent to:

SELECT e.*
FROM employees e
WHERE e.department_id = 20;

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

The keypart is incorrect qualified name:

SELECT * FROM employees
WHERE EXISTS(
SELECT * FROM departments
WHERE employees.department_id= 20);
      -- here should be departments.department_id

You are referring to column from outer query. What you want is:

SELECT * FROM employees
WHERE EXISTS(
SELECT * FROM departments
WHERE departments.department_id= 20);

Upvotes: 0

sagi
sagi

Reputation: 40471

Query 2 should be :

SELECT * FROM employees
WHERE EXISTS(
             SELECT * FROM departments
             WHERE departments.department_id= 20)

You're using employees alias, so when the employee department_id is different then 20 , the subquery returns no rows, regardless the fact that the condition is inside the subquery and not in the outer query .

Upvotes: 1

Related Questions