Exists and Correlated Subquery are tricky

Today is the second of our sql training. The stuff was too trivial yesterday. But it starts to get interesting now.

The subquery refers to those SELECT statements nested inside another (SELECT) query. Then the SQL got the structured. The nested query is normally called "sub query", "inner query" and "lower query" relative to "main query", "out query" and "upper query".

There is a lot going on in subquery. It's flexible, however, a lot of subqueries can be replaced by joins, which are more efficient.

A tricky example I found during the class is this one:

SELECT empno, bonus, comm
FROM emp
WHERE EXISTS (SELECT 'x' FROM dept WHERE mgrno=EMP.empno);

This query is designed to return employee number, bonus and commission for any employees who are listed as a manager on dept table.

There are 4 things that draw my attention:
(1) emp table and dept table are correlated in the sub select query.
(2) EXISTS is used. This is an operator I barely use.
(3) An alphabetic constant 'x' is used. It can be replaced with anything constant like 1 or 'y'. Because they simply don't make differences, since EXISTS only serves as "TRUE"/"FALSE".
(4) The way those queries became correlated is quite weird. You just call the emp table directly in the subquery. However if you try to run the subquery (SELECT 'x' FROM dept WHERE mgrno=EMP.empno) itself, you will get syntax error.

One thing needs to be pointed out is, if the two table are not correlated in the subquery, EXISTS will always return as TRUE, so you end up selecting every row in emp table.