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
Post a Comment