Archive

Archive for the ‘Oracle’ Category

Inner Join in Oracle 

October 8, 2011 Leave a comment

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate.

Here is the inner join example in oracle

--Create DEPARTMENT Table
CREATE TABLE DEPARTMENT(DEPT_ID  number(10) not null,
                        DEPT_NAME varchar2(50) not null,
                        CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPT_ID)
                        );

-- Insert Records into DEPARTMENT table

INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (1,'Data Group');
INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (2,'Technical');
INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (3,'Purchasing');
INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (4,'R and D');
INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (5,'Communication');

-- Create EMPLOYEE Table
CREATE TABLE EMPLOYEE(EMP_ID number(10) not null,
                      EMP_NAME varchar2(50) not null,
                      EMP_HIREDATE DATE,
                      EMP_DEPT_ID number(10),
                      EMP_SALARY number(6),
                      CONSTRAINT EMP_ID_PK PRIMARY KEY (EMP_ID),
                      CONSTRAINT FK_EMP_DEPT_ID FOREIGN KEY (EMP_DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
                      );

--Insert records into Employee table

INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1001,'Lawrence','21-JUN-2001',4,20000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1002,'NareshKumar','01-FEB-1996',3,30000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1003,'Praveen','07-OCT-1996',1,25000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1004,'Rajesh','10-MAY-1996',2,15000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1005,'Jaggap','13-SEP-2010',3,35000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1006,'Silva','20-JUL-2002',1,20000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1007,'Rejina','25-MAR-2001',5,42000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1008,'LisaMartin','28-MAR-2001',4,35000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1009,'Salmon','06-JAN-1998',4,30000);

Inner join Query:


SELECT E.EMP_NAME,D.DEPT_NAME FROM EMPLOYEE E INNER JOIN DEPARTMENT D
        ON E.EMP_DEPT_ID=D.DEPT_ID AND 
        D.DEPT_NAME='R and D';

Inner Join Query Result:
 
 

InnerJoin

InnerJoin

Advertisements

Outer join in Oracle

October 8, 2011 Leave a comment

The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.

 
 


--Create DEPARTMENT Table
CREATE TABLE DEPARTMENT(DEPT_ID  number(10) not null,
                        DEPT_NAME varchar2(50) not null,
                        CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPT_ID)
                        );

-- Insert Records into DEPARTMENT table

INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (1,'Data Group');
INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (2,'Technical');
INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (3,'Purchasing');
INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (4,'R and D');
INSERT INTO DEPARTMENT ( DEPT_ID, DEPT_NAME ) values (5,'Communication');

-- Create EMPLOYEE Table
CREATE TABLE EMPLOYEE(EMP_ID number(10) not null,
                      EMP_NAME varchar2(50) not null,
                      EMP_HIREDATE DATE,
                      EMP_DEPT_ID number(10),
                      EMP_SALARY number(6),
                      CONSTRAINT EMP_ID_PK PRIMARY KEY (EMP_ID),
                      CONSTRAINT FK_EMP_DEPT_ID FOREIGN KEY (EMP_DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
                      );
                      
--Insert records into Employee table

INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1001,'Lawrence','21-JUN-2001',4,20000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1002,'NareshKumar','01-FEB-1996',3,30000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1003,'Praveen','07-OCT-1996',1,25000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1004,'Rajesh','10-MAY-1996',2,15000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1005,'Jaggap','13-SEP-2010',3,35000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1006,'Silva','20-JUL-2002',1,20000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1007,'Rejina','25-MAR-2001',5,42000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1008,'LisaMartin','28-MAR-2001',4,35000);
INSERT INTO EMPLOYEE( EMP_ID,EMP_NAME,EMP_HIREDATE,EMP_DEPT_ID,EMP_SALARY ) VALUES (1009,'Salmon','06-JAN-1998',4,30000);

 
LEFT OUTER JOIN:
In the below query the left side table is Employee. So the left outer join will return the non matching rows from employee table.



SELECT E.EMP_NAME,D.DEPT_NAME FROM EMPLOYEE E 
        LEFT OUTER JOIN DEPARTMENT D 
        ON e.emp_dept_id=d.dept_id AND
           d.dept_name='R and D';


LeftOuterJoin

LeftOuterJoin

 
 
RIGHT OUTER JOIN:
In the below query the right side table is Department. So the right outer join will return the non matching columns from Department table.



SELECT E.EMP_NAME,D.DEPT_NAME FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D
        ON e.emp_dept_id=d.dept_id AND
           d.dept_name='R and D';


RightOuterJoin

RightOuterJoin

 
 
FULL OUTER JOIN:



SELECT E.EMP_NAME,D.DEPT_NAME FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D
        ON e.emp_dept_id=d.dept_id AND
           d.dept_name='R and D'; 


FullOuterJoin

FullOuterJoin