CODEWITHSUNDEEP
sql
enderunal
enderunal

Reputation: 54

What does this query means?

There is a table named Employee and it's have employee_id, manager_id, salary columns on it. My query is 

select employee_id,salary,last_name from employees M
WHERE EXISTS 
(SELECT employee_id FROM employees W
WHERE (W.manager_id = M.employee_id) AND W.SALARY>10000)
order by employee_id asc

What does this query means?

a)All managers whom salaries is greater than 10000

b)All managers whom have at least one employee making greater than 10000

Upvotes: 1

Views: 207

Answers (4)

PSR
PSR

Reputation: 40318

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. For example:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

In your case option B is correct.

Upvotes: 2

MilancheMax
MilancheMax

Reputation: 1

b) It says: give me all M(anagers) which have (at least one) W(orker) (under them) with salaries greater than 10k.

Upvotes: 0

Yogus
Yogus

Reputation: 2272

I wonder the type of questions being asked in stackoverflow.

So here I break your query 

SELECT employee_id FROM employees W
WHERE (W.manager_id = M.employee_id) AND W.SALARY>10000

The above query is searching for managers in your employees table whose salary is more than 10000

after that you have exists queryselect employee_id,salary,last_name from employees M to get the managers who are your employees and who staisfy the salary criteria in previous select query.

In short you will get the managers details who are earning more than 10000

Upvotes: 0

Daniel
Daniel

Reputation: 411

It would seem that it means all managers with at least one employee earning more than 10000. Its hard to say without seeing the actual tables etc.

Think about it like this:

The employee W has the manager_id = the employee_id of the employee M, and it is W how earns more than 10000.

So, M is the Manager, W is the employee and W earns more than 10000

Upvotes: 0

Related Questions