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: 
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.
Try this sub Query this will use sub query.

```
select ENAME,JOB from EMP where DEPTNO in (SELECT DEPTNO from DEPT where loc = 'CHICAGO');

```