Joins in Oracle

CREATE TABLE test_dept (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);

INSERT INTO  test_dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO  test_dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO  test_dept VALUES (30,'SALES','CHICAGO');
INSERT INTO  test_dept VALUES (40,'OPERATIONS','BOSTON');
COMMIT;


CREATE TABLE test_emp (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES test_dept(department_id)
);

INSERT INTO test_emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO test_emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO test_emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO test_emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO test_emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO test_emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO test_emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO test_emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO test_emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO test_emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO test_emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO test_emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO test_emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO test_emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

select * FROM test_dept;


DEPARTMENT_ID DEPARTMENT_NAM LOCATION     
------------- -------------- -------------
           10 ACCOUNTING     NEW YORK     
           20 RESEARCH       DALLAS       
           30 SALES          CHICAGO      
           40 OPERATIONS     BOSTON       


select * FROM test_emp;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE      SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- --------- ---------- ---------- -------------
       7369 SMITH      CLERK           7902 17-DEC-80        800                       20
       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300            30
       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500            30
       7566 JONES      MANAGER         7839 02-APR-81       2975                       20
       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400            30
       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                       30
       7782 CLARK      MANAGER         7839 09-JUN-81       2450                       10
       7788 SCOTT      ANALYST         7566 19-APR-87       3000                       20
       7839 KING       PRESIDENT            17-NOV-81       5000                       10
       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0            30
       7876 ADAMS      CLERK           7788 23-MAY-87       1100                       20

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE      SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- --------- ---------- ---------- -------------
       7900 JAMES      CLERK           7698 03-DEC-81        950                       30
       7902 FORD       ANALYST         7566 03-DEC-81       3000                       20
       7934 MILLER     CLERK           7782 23-JAN-82       1300                       10

14 rows selected. 



INNER JOIN: 
Example of an ANSI INNER JOIN
SELECT d.department_name,
       e.employee_name
FROM   test_dept d
       JOIN test_emp e ON d.department_id = e.department_id
WHERE  d.department_id >= 30
ORDER BY d.department_name;

DEPARTMENT_NAME EMPLOYEE_NAME
-------------- ----------
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

6 rows selected.

Example of Non-ANSI/Traditional INNER JOIN
SELECT d.department_name,
       e.employee_name
FROM   test_dept d, test_emp e
WHERE  d.department_id = e.department_id
AND    d.department_id >= 30
ORDER BY d.department_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

6 rows selected.


OUTER JOINS:
Left Outer Join: ANSI Format


SELECT d.department_name,
       e.employee_name     
FROM   test_dept d
       LEFT OUTER JOIN test_emp e ON d.department_id = e.department_id
WHERE  d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

7 rows selected.
Left Outer Join : Non ANSI/Traditional Format
Notice the "(+)" is used to indicate the side of the join condition that may be missing
i,e. Here (+) sign is on test_emp table's column, So result will be (Inner Join + data from  test_dept table.

SELECT d.department_name,
       e.employee_name      
FROM   test_dept d, test_emp e
WHERE  d.department_id = e.department_id (+) 
AND    d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

7 rows selected.
Right Outer Join:

 It returns all valid rows from the table on the right side of the JOIN keyword, along with the values from the table on the left side, or NULLs if a matching row doesn't exist. Note:
The following example has altered the order of the tables so a RIGHT [OUTER] JOIN is now required.
SELECT d.department_name,
       e.employee_name     
FROM   test_emp e
       RIGHT OUTER JOIN test_dept d ON e.department_id = d.department_id
WHERE  d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

7 rows selected.


Imp Note: Remember, the non-ANSI outer join syntax is not dependent on table order, so there is no real concept of right or left outer joins, just outer joins.
FULL Outer Join:
 combines all the rows from the tables on the left and right sides of the 
join. If there is a conventional match it is made. 
If either side has missing data, it is replaced by NULLs, rather than throwing the row away.

To see a working example, we need to add another employee who is not assigned to a department.
INSERT INTO employees VALUES (8888,'JONES','DBA',null,to_date('02-1-1982','dd-mm-yyyy'),1300,NULL,NULL);
COMMIT;
SELECT d.department_name,
       e.employee_name     
FROM   test_emp e
       FULL OUTER JOIN test_dept d ON e.department_id = d.department_id
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
OPERATIONS
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT
RESEARCH       SMITH
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD
               JONES

16 rows selected.