DATABASE (SQL) DAY 7 AND 8 ) JOINING , CLAUSE

                                       *JOINING*

JOINING IS A COMMUNICATON  BETWEEN TWO OR MORE TABLE.

* REDUCE THE COMPLECITY OF TABLE

* THERE ARE SOMETIMES OF JOINING USE THEN SQL.

* ON KEYWORD IS USED TO ACTIVATE THE JOIN CONDITION.

* JOINING IS TO BE RETURN NULL DATA VALUE . IF RECORD NOT FOUND.

* TYPES OF JOINING

  1)INNER JOIN

  2)LEFT OUTER JOIN

 3)RIGHT OUTER JON

 4) FULL OUTER JOIN 

                          *INNER JOIN*

INNER JOIN RETURN ALL ROW FROM MULTIPLE TABLE WHERE THE JOIN           CONDITION IS MEET.

* INNER JOIN ALWAYS RETURN EQUAL VALUE.

* QUERY:-

:- CREATE TABLE PRODUCT1 (PID INT,PRODUCT VARCHAR(50));

:-CREATE TABLE INVENTRY(PID INT,COST INT);


:-INSERT INTO PRODUCT1 VALUES(201,'HIMALYA');

:-INSERT INTO PRODUCT1 VALUES(202,'DABUR');

:-INSERT INTO PRODUCT1 VALUES(203,'PATANJALI');

:-INSERT INTO PRODUCT1 VALUES (206,'MAMAEARTH');


:-INSERT INTO INVENTRY VALUES(201,1998);

:-INSERT INTO INVENTRY VALUES(202,2098);

:-INSERT INTO INVENTRY VALUES(203,2198);

:-INSERT INTO INVENTRY VALUES(204,15000);


:-SELECT INVENTRY.PID,INVENTRY.COST,PRODUCT1.PRODUCT FROM INVENTRY INNER JOIN PRODUCT1 ON INVENTRY.PID=PRODUCT1.PID;


                          *LEFT OUTER JOIN* 

*MATCH ALL LEFT SIDE ENTRY.

* IF THE RECORD NOT FOUND IS NULL.

* QUERY:-

 :-SELECT INVENTRY.PID,INVENTRY.COST,PRODUCT1.PRODUCT FROM INVENTRY LEFT OUTER JOIN PRODUCT1 ON INVENTRY.PID=PRODUCT1.PID;

 

                          *RIGHT OUTER JOIN*

* MATCH TO THE RIGHT SIDE TABLE.

*QUERY :-

  :- SELECT INVENTRY.PID,INVENTRY.COST,PRODUCT1.PRODUCT FROM INVENTRY RIGHT OUTER JOIN PRODUCT1 ON INVENTRY.PID=PRODUCT1.PID;

                          *FULL OUTER JOIN*

* IT MATCH BOTH SIDE TABLE(left or right).

 *QUERY:-

 :- SELECT INVENTRY.PID,INVENTRY.COST,PRODUCT1.PRODUCT FROM INVENTRY FULL OUTER JOIN PRODUCT1 ON INVENTRY.PID=PRODUCT1.PID;

                          *EXIST CLAUSE*

* EXIST CLOUSE IS USE TO CHECK EXISTING OF RECORD BETWEEN TWO                TABLE. 


                             

JOINING 

Comments

Popular posts from this blog

DATA CONTROL ( Gridview , Repeater , Formview , DataList , Detailsview , Listview )

Z 5 ) MVC (Model view Controller)

Z 8 ) MVC HTMLHELPER METHOD AND ACTIONLINK (how to use css ,atrributes,style in css)