CODEWITHSUNDEEP
sqloracle-database
Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

Query is working in sql developer but not in sql plus and it should throw an error

enter image description here

select * 
from emp
where 
    (select salary from dual) in (select salary from employees);

EMP table has the same 107 which are in table EMPLOYEES. Here select salary from dual should produce an error in the where clause but it does not. How?

On the other hand it is returning rows in SQL Developer, but not in sqlplus. Why?

Upvotes: 1

Views: 1299

Answers (2)

Littlefoot
Littlefoot

Reputation: 143053

Good! Hopefully, it'll teach you to use table aliases. Without them, it was a column from the emp table; as it exist, no error was thrown.

I don't have your tables so Scott's will do.

SQL> create table employees as select * From emp where deptno = 10;

Table created.

SQL> select a.*
  2  from emp a
  3  where
  4      (select a.sal from dual d) in (select b.sal from employees b);

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7934 MILLER     CLERK           7782 23.01.82       1300                    10

SQL> select a.*
  2  from emp a
  3  where
  4      (select d.sal from dual d) in (select b.sal from employees b);
    (select d.sal from dual d) in (select b.sal from employees b)
            *
ERROR at line 4:
ORA-00904: "D"."SAL": invalid identifier


SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Why are you using a subquery when it is not necessary? This should do what you want:

select e.*
from emp e
where e.salary in (select salary from employees);

Upvotes: 0

Related Questions