SQL : Outer Join Case Study

Assuming we have two tables a and b

table a (emp_id, emp_name, dpt_id)
table b (dpt_id, dpt_name)

table a has dpt_id - 10, 20, 30, 40, 50
table b has dpt_id - 10, 20, 30, 40, 60

Six cases were tested. The following is the observation.

SQL>select emp_id, dpt_id, emp_name from a;

EMP_ID DPT_ID EMP_NAME
---------- ---------- ----------
1001 20 Saran
1002 20 Jason
1003 20 Brian
1004 10 Paul
1012 40 Mary
1006 30 Alex
1007 30 Steve
1008 20 Jim
1009 20 Mark
1010 20 Yihong
1013 30 Melanie
1011 50 Greathouse
1014 50 Jie
1015 50 Raj

14 rows selected.

SQL>select * from b

DPT_ID DPT_NAME
---------- ------------
10 Management
20 Development
30 Usability
40 HR
60 Sales

5 rows selected.

SQL>select emp_id, dpt_name from a, b where a.dpt_id = b.dpt_id;

EMP_ID DPT_NAME
---------- ------------
1001 Development
1002 Development
1003 Development
1004 Management
1012 HR
1006 Usability
1007 Usability
1008 Development
1009 Development
1010 Development
1013 Usability

11 rows selected.

SQL>select emp_id, dpt_name from a, b where a.dpt_id (+) = b.dpt_id

EMP_ID DPT_NAME
---------- ------------
1001 Development
1002 Development
1003 Development
1004 Management
1012 HR
1006 Usability
1007 Usability
1008 Development
1009 Development
1010 Development
1013 Usability
Sales

12 rows selected.

SQL>select emp_id, dpt_name from a, b
where a.dpt_id (+) = b.dpt_id and b.dpt_name = 'Development'

EMP_ID DPT_NAME
---------- ------------
1001 Development
1002 Development
1003 Development
1008 Development
1009 Development
1010 Development

6 rows selected.

SQL>select emp_id, dpt_name from a, b where a.dpt_id = b.dpt_id (+)

EMP_ID DPT_NAME
---------- ------------
1004 Management
1010 Development
1009 Development
1008 Development
1003 Development
1002 Development
1001 Development
1013 Usability
1007 Usability
1006 Usability
1012 HR
1015
1014
1011

14 rows selected.

SQL>select emp_id, dpt_name from a, b
where a.dpt_id = b.dpt_id (+) and b.dpt_name = 'Development'

EMP_ID DPT_NAME
---------- ------------
1001 Development
1002 Development
1003 Development
1008 Development
1009 Development
1010 Development

6 rows selected.

SQL>select emp_id, dpt_name from a, b
where a.dpt_id = b.dpt_id (+) and b.dpt_name(+) = 'Development'

EMP_ID DPT_NAME
---------- ------------
1010 Development
1009 Development
1008 Development
1003 Development
1002 Development
1001 Development
1015
1014
1011
1004
1013
1007
1006
1012

14 rows selected


------------------Scripts ----------------------------

CREATE TABLE a (emp_id NUMBER, emp_name VARCHAR2(50), dpt_id NUMBER)

CREATE TABLE b (dpt_id NUMBER, dpt_name VARCHAR2(50))

INSERT INTO B VALUES (10 , 'Management');
INSERT INTO B VALUES (20 , 'Development');
INSERT INTO B VALUES (30 , 'Usability');
INSERT INTO B VALUES (40 , 'HR');
INSERT INTO B VALUES (60 , 'Sales');



INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1001 , 20 , 'Saran' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1002 , 20 , 'Jason' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1003 , 20 , 'Brian' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1004 , 10 , 'Paul' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1012 , 40 , 'Mary' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1007 , 30 , 'Steve' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1008 , 20 , 'Jim' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1009 , 20 , 'Mark' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1010 , 20 , 'Yihong' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1013 , 30 , 'Melanie' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1011 , 50 , 'Greathouse' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1014 , 50 , 'Jie' );
INSERT INTO A (EMP_ID, DPT_ID, EMP_NAME) VALUES (1015 , 50 , 'Raj' );