SQL inner outer join

Document Sample
SQL inner outer join Powered By Docstoc
					INNER JOIN OPEARATION

SQL> describe empmstr;

 Name                            Null?      Type
 ------------------------------- --------   ----
 EMP_NO                                     NUMBER(2)
 FNAME                                      VARCHAR2(10)
 LNAME                                      VARCHAR2(10)
 BRANCH_NO                                  NUMBER(10)

SQL> select * from empmstr;

          EMP_NO FNAME       LNAME     BRANCH_NO
          --------- ---------- ---------- ---------
          1 VIJAY      CHAUHAN            1
          3 ROHIT      JOSHI              3
          4 ASHISH     JAIN               4

SQL> describe branchmstr;
 Name                            Null?      Type
 ------------------------------- --------   ----
 NAME                                       VARCHAR2(10)
 BRANCH_NO                                  NUMBER(3)

SQL> select * from branchmstr;

NAME       BRANCH_NO
---------- ---------
VIJAY              1
ALPHA              2
SIGMA              8
SOPHIE             3
ELECTRA            6
INDIANA            4

6 rows selected.

SQL> select E.emp_no,(E.fname || ' ' || E.lname) "Name",B.name "Branch"
  2   from empmstr E,branchmstr B where B.branch_no=E.branch_no;

          EMP_NO Name                  Branch
          --------- --------------------- ----------
          1 VIJAY CHAUHAN         VIJAY
          3 ROHIT JOSHI           SOPHIE
          4 ASHISH JAIN           INDIANA


OUTER     JOIN OPERATION

SQL> CREATE TABLE EMPMSTR(EMP_NO VARCHAR(5),FNAME VARCHAR(10), LNAME
VARCHAR(10));

Table created.

SQL> DESCRIBE EMPMSTR;

 Name                              Null?    Type

 ------------------------------- -------- ----

 EMP_NO                                     VARCHAR2(5)

 FNAME                                      VARCHAR2(10)

 LNAME                                      VARCHAR2(10)

SQL> INSERT INTO EMPMSTR VALUES('&EMP_NO','&FNAME','&LNAME');
Enter value for emp_no: A001

Enter value for fname: RAKESH

Enter value for lname: CHAUDHARY

old      1: INSERT INTO EMPMSTR VALUES('&EMP_NO','&FNAME','&LNAME')

new      1: INSERT INTO EMPMSTR VALUES('A001','RAKESH','CHAUDHARY')

1 row created.



SQL> /

Enter value for emp_no: B023

Enter value for fname: VIJAY

Enter value for lname: CHAUHAN

old      1: INSERT INTO EMPMSTR VALUES('&EMP_NO','&FNAME','&LNAME')

new      1: INSERT INTO EMPMSTR VALUES('B023','VIJAY','CHAUHAN')

1 row created.



SQL> /

Enter value for emp_no: A003

Enter value for fname: ADITYA

Enter value for lname: KUMAR

old      1: INSERT INTO EMPMSTR VALUES('&EMP_NO','&FNAME','&LNAME')

new      1: INSERT INTO EMPMSTR VALUES('A003','ADITYA','KUMAR')

1 row created.



SQL> SELECT * FROM EMPMSTR;

EMP_N FNAME          LNAME

----- ---------- ----------

A001     RAKESH      CHAUDHARY

B023     VIJAY       CHAUHAN

A003     ADITYA      KUMAR



SQL> SELECT * FROM CNTCDTLS;

CODE_N CN CNTC_DATA

------ -- ---------

a001      r       23432

a002      c       23445

a003      r        3243
a004      r       2424

1         r       2354

SQL> SELECT (E.FNAME || ' ' || E.LNAME) "NAME",C.CNTC_TYPE,C.CNTC_DATA

    2   FROM EMPMSTR E,CNTCDTLS C WHERE E.EMP_NO=C.CODE_NO(+);

NAME                     CN CNTC_DATA

--------------------- -- ---------

RAKESH CHAUDHARY

ADITYA KUMAR

VIJAY CHAUHAN