SQL Subquery with JOIN

Question

I'm trying to learn Subquerys.
I have troubles with this:

The two tables:

CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY,
LOC varchar2(3));

CREATE TABLE EMP
(ENAME varchar2(10),
JOB varchar2(9),
DEPTNO NUMBER(2) NOT NULL
CONSTRAINT EMP_FOREIGN_KEY REFERENCES DEPT (DEPTNO));

I want to get the name (emp.ename) and the job (emp.job) but only where the job also exists in 'CHICAGO'.

This is what I have done:

SELECT emp1.ename, emp1.job
FROM emp emp1
WHERE emp1.job EXISTS (SELECT emp2.job
                      FROM emp emp2
                      FULL JOIN dept ON (emp2.deptno = dept.deptno)
                      WHERE dept.loc = 'CHICAGO');

I always get the "invalid relational operator" error in the line 3.

Example of the outcome:

ENAME | JOB  | LOC 
JONES | SALE | CHICAGO 
FORD  | SALE | NEW YORK  //He doesn't sit in CHICAGO but the job also exists in Chicago 

Answer 1

To simplify your query, you can use:

select emp1.ename, emp1.job 
from EMP emp1 
where emp1.deptno in (SELECT DEPT.DEPTNO from DEPT where DEPT.loc = 'CHICAGO');

---

To select name and job from emp1 where emp1's job is same as emp2's job with location Chicago:

SELECT emp1.ename, emp1.job
FROM emp emp1
WHERE emp1.job IN (SELECT emp2.job
                      FROM emp emp2
                      FULL JOIN dept ON (emp2.deptno = dept.deptno)
                      WHERE dept.loc = 'CHICAGO');

---

To select name and job from emp1 if there exists atleast one record with location in chicago.

  SELECT emp1.ename, emp1.job
    FROM emp emp1
    WHERE  EXISTS (SELECT emp2.job
                   FROM emp emp2
                   FULL JOIN dept ON (emp2.deptno = dept.deptno)
                   WHERE dept.loc = 'CHICAGO');

---

In your case, i assume that the first and second query would be more appropriate.

Answer 2

The exists operator is applied on a subquery, not a column:

SELECT emp1.ename, emp1.job
FROM emp emp1
WHERE  EXISTS (SELECT emp2.job
               FROM emp emp2
               FULL JOIN dept ON (emp2.deptno = dept.deptno)
               WHERE dept.loc = 'CHICAGO');