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

I guess my second query would help you get the desired output. — ngrashia
– ngrashia, Commented Jul 15, 2014 at 7:13

ngrashia · Accepted Answer · 2014-07-15 06:57:00Z

6

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.

answered Jul 15, 2014 at 6:57

ngrashia

9,8945 gold badges45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nightfox79 Over a year ago

The first query and the second are the same, you should add a where clause where the ename from emp2 is different from the ename of emp1, then they are different and give you some different results.

ngrashia Over a year ago

@Bajellor: I guess my second query would help you get the desired output

Mureinik · Accepted Answer · 2014-07-15 06:56:54Z

1

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');

answered Jul 15, 2014 at 6:56

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Comments

smn_onrocks · Accepted Answer · 2014-07-15 06:58:29Z

1

Try this sub Query this will use sub query.

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

answered Jul 15, 2014 at 6:58

smn_onrocks

1,3421 gold badge19 silver badges34 bronze badges

2 Comments

Fabian Bachl Over a year ago

Doesn't work like this I need where the job also exists in chicago not where the worker works in chicago

smn_onrocks Over a year ago

Your requirement is not very clear to me will u please explain it in little deep and give some sample data output so that i can help u.. Thanku.

Arion · Accepted Answer · 2014-07-15 06:57:22Z

0

Maybe something like this:

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

answered Jul 15, 2014 at 6:57

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Comments

dnoeth · Accepted Answer · 2014-07-15 07:06:19Z

0

You're mixing IN-Subqueries and Correlated Subqueries (and do an unnecessary FULL join):

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

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

answered Jul 15, 2014 at 7:06

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Collectives™ on Stack Overflow

SQL Subquery with JOIN

5 Answers 5

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related