Hi,
The EXIST predicate is always followed by a subselect.

For example the get the rows for values in department which have deptno which matches a workdept in the employee table you'd do:

SELECT deptno, deptname FROM department WHERE EXISTS (SELECT workdept FROM employee WHERE workdept = dept)


This can also be done by just using a join.

SELECT department.deptno, department.deptname FROM department, employee WHERE department.deptno = employee.workdept;

What's the pro's and con's of both approaches?

Thanks

You can try yourself.

Second query(using a join) will return multiple rows for a department.
Exact number of rows for a department will be the number of employees belonging to the department.

If you want to get same result as first query, you should add DISTINCT keyword for second query.

Ok so same question with DISTINCT included?

Thanks.

I want to say samethings as Marcus A in this thread
Using equals or IN

A good rule-of-thumb, in general, is to try putting into subqueries (with EXISTS or IN) all tables from which no direct information (i.e.: column values or counts) are needed.

This will most often be much more readable that the JOIN, and most often also more performant. Beware: a join could be more performant than an EXISTS. If it really matters: use EXPLAIN to find out, and document the reason for your choice (using "--" comments in SQL).