Use in operator with table join : Table Join Column « Table Joins « Oracle PL / SQL






Use in operator with table join

   
SQL>
SQL> CREATE TABLE departments
  2  (department_id           number(10)            not null,
  3   department_name         varchar2(50)          not null,
  4   CONSTRAINT departments_pk PRIMARY KEY (department_id)
  5  );

Table created.

SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 1,             'Data Group' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 2,             'Purchasing' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 3,             'Call Center' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 4,             'Communication' );

1 row created.

SQL>
SQL>
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6),
 10    CONSTRAINT           employees_pk PRIMARY KEY (employee_id),
 11    CONSTRAINT           fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id)
 12  );

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL> select * from employees;

EMPLOYEE_ID LAST_NAME                                          EMAIL                          HIRE_DATE JOB_ID                         DEPARTMENT_ID     SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
       1001 Lawson                                             lawson@g.com                   01-JAN-02 MGR                                        1      30000       1004
       1002 Wells                                              wells@g.com                    01-JAN-02 DBA                                        2      20000       1005
       1003 Bliss                                              bliss@g.com                    01-JAN-02 PROG                                       3      24000       1004
       1004 Kyte                                               tkyte@a.com                    13-JUN-98 MGR                                        4      25000       1005
       1005 Viper                                              sdillon@a .com                 10-JUN-08 PROG                                       1      20000       1006
       1006 Beck                                               clbeck@g.com                   10-JUN-08 PROG                                       2      20000
       1007 Java                                               java01@g.com                   10-JUN-08 PROG                                       3      20000       1006

EMPLOYEE_ID LAST_NAME                                          EMAIL                          HIRE_DATE JOB_ID                         DEPARTMENT_ID     SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
       1008 Oracle                                             oracle1@g.com                  10-JUN-08 DBA                                        4      20000       1006

8 rows selected.

SQL>
SQL> select * from departments;

DEPARTMENT_ID DEPARTMENT_NA
------------- -------------
            1 Data Group
            2 Purchasing
            3 Call Center
            4 Communication

4 rows selected.

SQL>
SQL>
SQL> select e.employee_id, e.last_name, d.department_name
  2        from employees e, departments d
  3       where d.department_name in ('Data Group','Purchasing')
  4         and e.department_id = d.department_id
  5      /

EMPLOYEE_ID LAST_NAME                                          DEPARTMENT_NA
----------- -------------------------------------------------- -------------
       1001 Lawson                                             Data Group
       1005 Viper                                              Data Group
       1002 Wells                                              Purchasing
       1006 Beck                                               Purchasing

4 rows selected.

SQL>
SQL> drop table employees cascade constraints;

Table dropped.

SQL> drop table departments cascade constraints;

Table dropped.

SQL>

   
    
  








Related examples in the same category

1.Join two tables: column is not mentioned in the SELECT list
2.Join with (+)
3.Use column name alias during table join
4.3-Way join
5.Join and GROUP BY
6.Join three table to find out which employee sold that gift
7.Join using and join on
8.Join using clause
9.Join columns with full table name reference