Not equals and subquery : Subquery Basics « Subquery « Oracle PL / SQL






Not equals and subquery

    

SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;

Table created.

SQL> insert into emp values(1,'Tom','N',   'TRAINER', 13,date '1965-12-17',  800 , NULL,  20);

1 row created.

SQL> insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);

1 row created.

SQL> insert into emp values(3,'Wil','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);

1 row created.

SQL> insert into emp values(4,'Jane','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);

1 row created.

SQL> insert into emp values(5,'Mary','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);

1 row created.

SQL> insert into emp values(6,'Black','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);

1 row created.

SQL> insert into emp values(7,'Chris','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);

1 row created.

SQL> insert into emp values(8,'Smart','SCJ', 'TRAINER', 4,date '1959-11-26',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(9,'Peter','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);

1 row created.

SQL> insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);

1 row created.

SQL> insert into emp values(11,'Ana','AA',  'TRAINER', 8,date '1966-12-30',  1100, NULL,  20);

1 row created.

SQL> insert into emp values(12,'Jane','R',   'Manager',   6,date '1969-12-03',  800 , NULL,  30);

1 row created.

SQL> insert into emp values(13,'Fake','MG',   'TRAINER', 4,date '1959-02-13',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(14,'Mike','TJA','Manager',   7,date '1962-01-23',  1300, NULL,  10);

1 row created.

SQL>
SQL> create table departments
  2  ( deptno NUMBER(2)     constraint D_PK
  3                         primary key
  4  , dname  VARCHAR2(10)
  5  , location VARCHAR2(8)
  6  , mgr    NUMBER(4)
  7  ) ;

Table created.

SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7);

1 row created.

SQL> insert into departments values (20,'TRAINING',  'DALLAS',  4);

1 row created.

SQL> insert into departments values (30,'SALES',     'CHICAGO', 6);

1 row created.

SQL> insert into departments values (40,'HR',        'BOSTON',  9);

1 row created.

SQL>
SQL> select empno
  2  from   emp
  3  where  deptno <> (select deptno
  4                    from   departments
  5                    where  dname = 'TRAINING');
Enter...

     2
     3
     5
     6
     7
     9
    10
    12
    14

9 rows selected.

SQL>
SQL> drop table departments;

Table dropped.

SQL> drop table emp;

Table dropped.

   
    
    
    
  








Related examples in the same category

1.Use sub query as a virtual table
2.Compare with data from subquery
3.An example of a nested three-level subquery
4.If an inner query returns a NULL, the outer query also returns NULL
5.Working with multi-column subqueries
6.Use aggregate function in sub query
7.Sub query: 'SELECT 1 FROM dept d'
8.Using the EXISTS and NOT EXISTS operators
9.Writing Single Row Subqueries
10.Compare with the sub query result
11.Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
12.Subqueries in a FROM Clause (Inline Views)
13.Single Row Subqueries May Return a Maximum of One Row
14.Subqueries May Not Contain an ORDER BY Clause
15.Sub query with table join
16.Writing Multiple Column Subqueries with table join
17.Subquery just returns a literal value: improve performance of your query
18.EXISTS typically offers better performance than IN with subqueries
19.NVL() is used to convert null in correlated query
20.Writing Nested Subqueries
21.subqueries in the SELECT column list (New Way)
22.subqueries in the SELECT column list (Old way)
23.Greater than average salary
24.Greater than max(salary)
25.Larger than value from subquery
26.Subquery in select statement
27.Subquery in where clause
28.Subqueries That Return Multiple Results
29.Subqueries in the WHERE Clause: equals
30.Subqueries in the WHERE Clause: less than
31.Simple Subqueries in select statement
32.Single-row subqueries return only one row of result.
33.The parent query of a single-row subquery can return more than one row.
34.Born after employee 4 was born
35.inline view: What percentage of these items exist in each bin selected