Saturday, January 17, 2015

Right Outer Join in SQL / Oracle Database

This can be used to demonstrate the Right outer join. ( Demonstrated using Oracle Database )

CREATE TABLE emp
(
  emp_id NUMBER(2) CONSTRAINT emp_col1_pk PRIMARY KEY,
  emp_name  VARCHAR2(20),
  emp_salary NUMBER(5)
);
CREATE TABLE dept
(
  dept_id NUMBER(2) CONSTRAINT dept_coll_pk PRIMARY KEY,
  dept_name VARCHAR(20),
  emp_id CONSTRAINT dept_col3_fk REFERENCES emp(emp_id)
);
-- Insert---

INSERT INTO "HR"."EMP" VALUES('1','Achala','1500');
INSERT INTO "HR"."EMP" VALUES('2','ach','1000');
INSERT INTO "HR"."EMP" VALUES('3','Tendulkar','20000');
INSERT INTO "HR"."EMP" VALUES('4','Jayasuriya','2000');

INSERT INTO "HR"."DEPT" (DEPT_ID,DEPT_NAME,EMP_ID) VALUES('1','Sports','1');
INSERT INTO "HR"."DEPT"(DEPT_ID,DEPT_NAME,EMP_ID) VALUES('2','Accounts','2');
INSERT INTO "HR"."DEPT" (DEPT_ID,DEPT_NAME,EMP_ID)VALUES('3','Sales','3');
INSERT INTO "HR"."DEPT" (DEPT_ID,DEPT_NAME) VALUES('4','Marketing');
INSERT INTO "HR"."DEPT" (DEPT_ID,DEPT_NAME) VALUES('5','Finance');

select * from emp;
select * from dept;
 
SELECT emp_name,dept_name FROM dept RIGHT OUTER JOIN emp ON (emp.emp_id = dept.emp_id);

SELECT emp_name,dept_name FROM dept RIGHT OUTER JOIN emp using (EMP_ID);






No comments:

Post a Comment

Using Zotero for academic writing