JOINS:
*******
-->It is used to retrieve the data from more than 1 table.
STUDENT: COURSE:
******** *****************
SNO SNAME CID CID CNAME FEE
1 MOHAN 10 10 ORACLE 2000
2 VINOD 20 20 JAVA 3000
3 MADAN 30 30 SAP 4000
4 RAJA 10 40 C++ 1500
5 RANI 10
6 SOWMYA 30
7 HARIKA 20
8 AJAY 10
9 VANI 30
10 RANA null
Types of Joins:
****************
1)SIMPLE JOIN
--EQUI JOIN
--NON -EQUI JOIN
2)CROSS JOIN OR CARTISEAN JOIN
3)INNER JOIN
4)OUTER JOIN
--LEFT OUTER JOIN
--RIGHT OUTER JOIN
--FULL OUTER JOIN
5)NATURAL JOIN
6)SELF JOIN
-->When we are retrieving data from multiple tables based on "WHERE" clause condition then we called as NON-ANSI format join.
-->When we are retrieving data from multiple tables with "ON" / "USING" clause condition then we called as ANSI format join.
SYNTAX FOR JOINS:
*****************
SELECT * FROM TABLE NAME1,TABLE NAME2 WHERE <JOIN CONDITION>;
SYNTAX FOR ANSI JOINS:
**********************
SELECT * FROM <TABLE NAME1> <JOIN KEY> <TABLE NAME2 > ON <JOIN CONDITION>;
EQUI JOIN:
**********
-->Retrieving data from multiple tables based on "eual operator(=)" is called equi join.
-->Equi join always retrievng only matching data/matching rows only.
-->Common column or common field data type must be match.
SYNTAX:
*******
SELECT *
FROM TABLENAME1,
TABLENAME2
WHERE <TABLE NAME1>.<COMMON COLUMN> = <TABLE NAME2>.<COMMON COLUMN>;
(OR)
WHERE <TN1 ALIAS NAME>.<COMMON COLUMN> = <TN2 ALIAS NAME>.<COMMON COL>;
EX:
***
CREATE TABLE XX_COURSE
(
CNO NUMBER PRIMARY KEY,
CNAME VARCHAR2(30),
FEE NUMBER
);
/
CREATE TABLE XX_STUDENT
(
SNO NUMBER PRIMARY KEY,
SNAME VARCHAR2(30),
CNO NUMBER REFERENCES XX_COURSE(CNO)
);
/
CREATE TABLE XX_SALGRADE
(
GRADE NUMBER,
LOWSAL NUMBER,
HIGHSAL NUMBER
);
/
INSERT INTO XX_COURSE VALUES(10,'ORACLE',3000);
INSERT INTO XX_COURSE VALUES(20,'JAVA',5000);
INSERT INTO XX_COURSE VALUES(30,'C++',2000);
INSERT INTO XX_COURSE VALUES(40,'C',1000);
/
INSERT INTO XX_STUDENT VALUES(100,'MOHAN',10);
INSERT INTO XX_STUDENT VALUES(101,'MADAN',20);
INSERT INTO XX_STUDENT VALUES(102,'VINOD',30);
INSERT INTO XX_STUDENT VALUES(103,'RAJA',40);
INSERT INTO XX_STUDENT VALUES(104,'RANI',50);
INSERT INTO XX_STUDENT VALUES(105,'RAMESH',10);
/
INSERT INTO XX_SALGRADE VALUES(1,1000,1999);
INSERT INTO XX_SALGRADE VALUES(2,2000,3999);
INSERT INTO XX_SALGRADE VALUES(3,4000,6000);
/
EX1:
****
WAQ TO RETRIEVE STUDENT AND THE CORRESPONDING COURSE DETAILS FROM
STUDENT,COURSE TABLES BY USING EQUI JOIN ?
SELECT * FROM XX_STUDENT,XX_COURSE WHERE CNO=CNO;
ERROR at line 1:
ORA-00918: column ambiguously defined
NOTE: IN ABOVE EXAMPLE WE GET AN ERROR IS "column ambiguously defined".
TO OVER COME THIS ERROR THEN WE SHOULD USE A TABLE NAME AS AN IDENTITY
TO AMBIGUOUSE COLUMN CNO LIKE BELOW,
SELECT * FROM XX_STUDENT,XX_COURSE WHERE XX_STUDENT.CNO=XX_COURSE.CNO;
(OR)
SELECT * FROM XX_STUDENT S,XX_COURSE C WHERE S.CNO=C.CNO;
/
select s.sno,
s.sname,
c.cname,
c.fee,
c.cno
from XX_student s,
XX_course c
where s.cno=c.cno;
and c.cno=10;
/SELECT APS.VENDOR_ID,
APS.VENDOR_NAME SUPPLIER_NAME,
APS.SEGMENT1 SUPPLIER_NUM,
APS.VENDOR_TYPE_LOOKUP_CODE,
ASSA.VENDOR_SITE_CODE,
ASSA.VENDOR_SITE_ID,
ASSA.ADDRESS_LINE1,
ASSA.COUNTRY,
ASSA.STATE,
ASSA.CITY,
HOU.NAME OPERATING_UNIT,
HOU.ORGANIZATION_ID
FROM AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL ASSA,
HR_OPERATING_UNITS HOU
WHERE APS.VENDOR_ID=ASSA.VENDOR_ID
AND ASSA.ORG_ID=HOU.ORGANIZATION_ID
--AND APS.VENDOR_NAME='Kingston, Max'
/
SELECT APS.VENDOR_NAME SUPPLIER_NAME,
APS.SEGMENT1 SUPPLIER_NUM,
AIA.INVOICE_NUM,
AIA.INVOICE_AMOUNT,
AIA.INVOICE_DATE,
AIA.INVOICE_CURRENCY_CODE,
ACA.CHECK_NUMBER
FROM AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_SUPPLIERS APS,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA
WHERE AIA.INVOICE_ID=AIDA.INVOICE_ID
AND AIA.INVOICE_ID=AIPA.INVOICE_ID
AND AIPA.CHECK_ID=ACA.CHECK_ID
AND APS.VENDOR_ID=AIA.VENDOR_ID
AND AIA.INVOICE_NUM='19879-781';
/
RULE OF JOIN:
=============
A ROW IN A FIRST TABLE IS COMPARING THE GIVEN JOIN CONDITION WITH ALL ROWS
OF SECOND TABLE.
EX2:
WAQ TO RETRIEVE STUDENT,COURSE DETAILS FROM TABLES IF CNO IS 20 ?
SELECT * FROM XX_STUDENT S,XX_COURSE C WHERE S.CNO=C.CNO AND C.CNO=20;
EX3:
WAQ TO RETRIEVE LIST OF EMPLOYEE FROM EMP,DEPT TABLES BY USING EQUI JOIN WHO ARE WORKING IN THE LOCATION IS 'CHICAGO' ?
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND LOC='CHICAGO';
EX4:
WAQ TO DISPLAY SUM OF SALARIES OF DEPARTMENTS FROM EMP,DEPT TABLES BY USING EQUI JOIN ?
SELECT DNAME,SUM(SAL) FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY DNAME;
EX5:
WAQ TO DISPLAY SUM OF SALARIES OF DEPARTMENTS FROM EMP,DEPT TABLES BY USING EQUI JOIN IF SUM OF SALARIES OF DEPARTMENTS ARE MORE THAN
10000?
SELECT DNAME,SUM(SAL) FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO
GROUP BY DNAME HAVING SUM(SAL)>10000;
NON-EQUI JOIN:
**************
-->
-->RETRIEVING DATA FROM MULTIPLE TABLES BASED ON ANY CONDITION EXCEPT EQUAL OPERATOR CONDITION IS CALLED AS NON-EQUI JOIN.
-->IN THIS JOIN WE CAN USE THE FOLLOWING OPERATORS ARE <,>,<=,>=,AND,BETWEEN,.........etc.
EX1:
****
SELECT *
FROM EMP E,
DEPT D
WHERE E.DEPTNO!=D.DEPTNO
AND D.DEPTNO=10;
EX2:
****
WAQ TO DISPLAY ENAME,SALARY,LOW SALARY,HIGH SALARY FROM EMP,SALGRADE TABLES WHOSE SALARY BETWEEN LOW SALARY AND HIGH SALARY ?
SELECT ENAME,SAL,LOWSAL,HIGHSAL FROM EMP,XX_SALGRADE
WHERE SAL BETWEEN LOWSAL AND HIGHSAL;
(OR)
SELECT ENAME,SAL,LOWSAL,HIGHSAL FROM EMP,XX_SALGRADE
WHERE (SAL>=LOWSAL) AND (SAL<=HIGHSAL);
/
SELECT E.ENAME,E.SAL,S.LOWSAL,S.HIGHSAL ,
CASE
WHEN SAL>=1000 AND SAL<1999 THEN 'LOW SAL'
WHEN SAL>=2000 AND SAL<3999 THEN 'MEDIUM SAL'
WHEN SAL>=4000 AND SAL<6000 THEN 'HIGH SAL'
ELSE 'NO GRADE'
END RESULT
FROM EMP E,
XX_SALGRADE S
WHERE E.SAL BETWEEN S.LOWSAL AND S.HIGHSAL;
/
CROSS JOIN / CARTESIAN JON:
***************************
-->Used to retrieve the data from multiple tables with out any condition is called as cross join/cartesian join.
--> IN CROSS JOIN,EACH ROW OF THE FIRST TABLE WILL JOIN JOINS WITH EACH ROW OF THE SECOND TABLE.THAT MEANS A FIRST TABLE IS HAVING "m" NO.OF
ROWS AND A SECOND TABLE IS HAVING "n" NO.OF ROWS THEN THE RESULT IS mXn NO.OF ROWS.
EX:
***
SELECT * FROM XX_STUDENT CROSS JOIN XX_COURSE;---ANSI
(OR)
SELECT * FROM XX_STUDENT,XX_COURSE; ----NON-ANSI
/
select empno, ename, sal, job, emp.deptno, dname, loc from emp ,dept ;
/
INNER JOIN:
***********
--> Inner Join is similar to Equi Join.Retrieving Data from multiple tables with "ON" Clause Condition.
SYNTAX:
********
ON <TABLE NAME1>.<COMMON COLUMN> = <TABLE NAME2>.<COMMON COLUMN>;
(OR)
ON <TN1 ALIAS NAME>.<COMMON COLUMN> = <TN2 ALIAS NAME>.<COMMON COLUMN>
;
Inner join sql syntax:
***********************
select table1.*,table2.* from table1,table2 where table1.commoncol=table2.commoncol
inner join ansi syntax:
*************************
select table1.*,table2.* from table1 inner join table2 on table1.commoncol=table2.commoncol
SELECT *
FROM TABLENAME1 ALIASNAME1
INNER JOIN
TABLENAME2 ALIASNAME2
ON TABLENAME1 ALIASNAME1.COMMONCOLUMN=TABLENAME2 ALIASNAME2.ALIASNAME
Inner join ansi syntax (3 tables)
**********************************
select table1.*,table2.*,table3.* from table1
inner join table2 on table1.commoncol=table2.commoncol
inner join table3 on table2.commoncol=table3.commoncol
Inner join ansi syntax (4 tables)
**********************************
select table1.*,table2.*,table3.*,table4.* from table1
inner join table2 on table1.commoncol=table2.commoncol
inner join table3 on table2.commoncol=table3.commoncol
inner join table4 on table3.commoncol=table4.commoncol;
EX1:
****
WAQ TO RETRIEVE STUDENT,COURSE DETAILS FROM TABLES BY USING INNER JOIN?
INNER JOIN:
***********
SELECT *
FROM XX_STUDENT
INNER JOIN
XX_COURSE
ON STUDENT.CNO=COURSE.CNO;
/
SELECT *
FROM XX_STUDENT S
INNER JOIN
XX_COURSE C
USING(CNO);
/
SELECT AIA.INVOICE_NUM,APS.VENDOR_NAME,ACA.CHECK_NUMBER
FROM AP_INVOICES_ALL AIA
INNER JOIN
AP_SUPPLIERS APS
ON AIA.VENDOR_ID=APS.VENDOR_ID
INNER JOIN
AP_INVOICE_DISTRIBUTIONS_ALL AIDA
ON AIA.INVOICE_ID=AIDA.INVOICE_ID
INNER JOIN
AP_INVOICE_PAYMENTS_ALL AIPA
ON AIPA.INVOICE_ID=AIA.INVOICE_ID
INNER JOIN
AP_CHECKS_ALL ACA
ON AIPA.CHECK_ID=ACA.CHECK_ID
AND AIA.INVOICE_NUM='19879-781';
/
EX1:
WAQ TO RETRIEVE STUDENT,COURSE DETAILS FROM TABLES BY USING INNER JOIN?
SOL:
SQL> SELECT * FROM XX_STUDENT INNER JOIN XX_COURSE ON STUDENT.CNO=COURSE.CNO;
EX2:
WAQ TO DISPLAY EMPLOYEE FROM EMP,DEPT TABLES BY USING INNER JOIN WHO ARE WORKING IN THE LOCATION IS "CHICAGO" ?
SOL:
SQL> SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO AND LOC='CHICAGO';
(OR)
SQL> SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
WHERE LOC='CHICAGO';
JOIN WITH ON:
**************
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.DEPTNO
,D.DNAME
,D.LOC
FROM EMP E
JOIN
DEPT D
ON(E.DEPTNO=D.DEPTNO);
JOIN WITH USING:
****************
SELECT EMPNO
,ENAME
,JOB
,SAL
,DEPTNO
,DNAME
,LOC
FROM EMP
JOIN
DEPT
USING(DEPTNO);
/
EQUI JON:
*********
SELECT *
FROM XX_STUDENT,
XX_COURSE
WHERE STUDENT.CNO=COURSE.CNO;
EX2:
WAQ TO DISPLAY EMPLOYEE FROM EMP,DEPT TABLES BY USING INNER JOIN WHO ARE WORKING IN THE LOCATION IS "CHICAGO" ?
SELECT *
FROM EMP E
INNER JOIN
DEPT D
ON E.DEPTNO=D.DEPTNO
AND LOC='CHICAGO';
OUTER JOINS:
=============
--> In the above Equi/Inner join we are retrievng only matching rows but not unmatching rows from mutilple tables.
So to overcome this problem then we use "Outer Joins".
Outer Joins are classified into 3 types.
****************************************
-->Left Outer Join
-->Right Outer Join
-->Full Outer Join
LEFT OUTER JOIN:
****************
-->It is used to display the full details of the left table and matched records of the right table.
-->Retrieving all rows(Matching and un matching) from left side table but retrieving matching rows from right side table.
ANSI FORMAT:
************
SELECT * FROM XX_STUDENT S LEFT OUTER JOIN XX_COURSE C ON S.CNO=C.CNO;
NON - ANSI FORMAT:
*****************
-->WHEN WE WRITE OUTER JOINS IN NON-ANSI FORMAT THEN WE SHOULD USE JOIN OPERATOR (+) .
EX:
***
SELECT * FROM XX_STUDENT S,XX_COURSE C WHERE S.CNO=C.CNO(+);
/
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEEPTNO(+);
/
--EQUI JOIN
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.COMM
,E.DEPTNO
,D.DEPTNO
,D.DNAME
,D.LOC
FROM EMP1 E,
DEPT D
WHERE 1=1
AND E.DEPTNO=D.DEPTNO;
/
DROP TABLE EMP1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
INSERT INTO EMP1(EMPNO,ENAME,JOB,SAL) VALUES (1234,'MOHAN','SOFTWARE',6000);
/
--LEFT OUTER JOIN
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.COMM
,E.DEPTNO
,D.DEPTNO
,D.DNAME
,D.LOC
FROM EMP E,
DEPT D
WHERE 1=1
AND E.DEPTNO=D.DEPTNO(+);
/
--USING ANOTHER WAY
SELECT *
FROM EMP1 E
LEFT OUTER JOIN
DEPT D
ON E.DEPTNO=D.DEPTNO;
/
--USING ANOTHER WAY
SELECT *
FROM EMP1 E
LEFT JOIN
DEPT D
ON E.DEPTNO=D.DEPTNO;
/
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.DEPTNO
,D.DNAME
,D.LOC
FROM EMP E
LEFT OUTER JOIN
DEPT D
ON (E.DEPTNO=D.DEPTNO);
/
RIGHT OUTER JOIN:
****************
-->It is used to display the full details of the right table and matched records of the left table.
-->Retrieving all rows(Matching and un matching) from right side table but retrieving matching rows from left side table.
ANSI FORMAT:
************
SELECT * FROM XX_STUDENT S RIGHT OUTER JOIN XX_COURSE C ON S.CNO=C.CNO;
NON-ANSI FORMAT:
***************
SQL> SELECT * FROM XX_STUDENT S,XX_COURSE C WHERE S.CNO(+)=C.CNO;
/
--EQUI JOIN
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.COMM
,E.DEPTNO
,D.DEPTNO
,D.DNAME
,D.LOC
FROM EMP E,
DEPT1 D
WHERE 1=1
AND E.DEPTNO=D.DEPTNO;
/
INSERT INTO DEPT1 VALUES (50,'HR','HYD');
/
--RIGHT OUTER JOIN
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.COMM
,E.DEPTNO
,D.DEPTNO
,D.DNAME
,D.LOC
FROM EMP1 E,
DEPT1 D
WHERE 1=1
AND E.DEPTNO(+)=D.DEPTNO;
/
--USING ANOTHER WAY
SELECT *
FROM EMP1 E
RIGHT OUTER JOIN
DEPT1 D
ON D.DEPTNO=E.DEPTNO;
/
--USING ANOTHER WAY
SELECT *
FROM EMP1 E
RIGHT JOIN
DEPT D
ON D.DEPTNO=E.DEPTNO;
/
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.DEPTNO
,D.DNAME
,D.LOC
FROM EMP1 E
RIGHT OUTER JOIN
DEPT1 D
ON (E.DEPTNO=D.DEPTNO);
/
FULL OUTER JOIN:
****************
-->If you join left and right outer joins with union operators such joins are called as full outer join.
-->Retrieving matching and unmatching rows from both sides tables.
ANSI FORMAT:
************
SELECT * FROM XX_STUDENT S FULL OUTER JOIN XX_COURSE C ON S.CID=C.CID;
NON - ANSI FORMAT:
*****************
SELECT * FROM XX_STUDENT S,XX_COURSE C WHERE S.CID(+)=C.CID(+);
ERROR at line 1:
ORA-01468: a predicate may reference only one outer-joined table
NOTE: NON-ANSI FORMAT IS NOT SUPPORTING FULL OUTER JOIN MECHANISM.SO THAT
WHEN WE WANT TO IMPLEMENT FULL OUTER JOIN IN NON -ANSI FORMAT THEN WE COMBINED THE RESULTS OF LEFT OUTER AND RIGHT OUTER JOINS BY USING "UNION"
OPERATOR.
EX:
****
SELECT * FROM XX_STUDENT S,XX_COURSE C WHERE S.CNO=C.CNO(+)
UNION
SELECT * FROM XX_STUDENT S,XX_COURSE C WHERE S.CNO(+)=C.CNO;
/
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.COMM
,E.DEPTNO
,D.DEPTNO
,D.DNAME
,D.LOC
FROM EMP E,
DEPT D
WHERE 1=1
AND E.DEPTNO=D.DEPTNO(+)
UNION
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.COMM
,E.DEPTNO
,D.DEPTNO
,D.DNAME
,D.LOC
FROM EMP E,
DEPT D
WHERE 1=1
AND E.DEPTNO(+)=D.DEPTNO;
/
--USING ANOTHER WAY
SELECT *
FROM EMP1 E
FULL OUTER JOIN
DEPT1 D
ON D.DEPTNO=E.DEPTNO;
/
--USING ANOTHER WAY
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.COMM
,E.DEPTNO
,D.DEPTNO
,D.DNAME
,D.LOC
FROM EMP1 E
FULL OUTER JOIN
DEPT1 D
ON D.DEPTNO=E.DEPTNO;
/
SELECT E.EMPNO
,E.ENAME
,E.JOB
,E.SAL
,E.DEPTNO
,D.DNAME
,D.LOC
FROM EMP1 E
FULL JOIN
DEPT1 D
ON (E.DEPTNO=D.DEPTNO);
NATURAL JOIN:
************
-->NATURAL JOIN IS SIMILAR TO EQUI JOIN.WHEN WE USE NATURAL JOIN WE SHOULD HAVE A COMMON COLUMN NAME.THIS COLUMN DATATYPE MUST BE MATCH.
--> WHENEVER WE ARE USING NATURAL JOIN THERE IS NO NEED TO WRITE A JOINING CONDITION BY EXPLICITLY BECAUSE INTERNALLY ORACLE SERVER IS PREPARING JOINING CONDITION BASED ON AN "EQUAL OPERATOR(=)" WITH COLUMN COLUMN NAME AUTOMATICALLY.
--> BY USING NATURAL JOIN WE AVOID DUPLICATE COLUMNS WHILE RETRIEVING DATA FROM MULTIPLE TABLES.
EX:
SELECT * FROM XX_STUDENT S NATURAL JOIN XX_COURSE C;
SELECT EMPNO,ENAME,SAL,JOB,DEPTNO,DNAME,LOC FROM EMP NATURAL JOIN DEPT;
/
SELECT *
FROM EMP E
NATURAL JOIN
DEPT
WHERE DEPTNO IN (10,20);
SELF JOIN:
**********
-->Joining a table data by itself is called as self join.
-->In self join a row in one table joined with the row of same table.
-->We can create any number of alias names on a single table by each alias name should be different name.
-->When we compare a column value with in the same table.
- SELF JOIN CAN BE IMPLEMENTED AT TWO SITUATIONS:
1. COMPARING A SINLGE COLUMN VALUES BY ITSELF IN THE TABLE.
2. COMPARING TWO DIFFERENT COLUMNS VALUES TO EACH OTHER IN THE TABLE.
EX.ON COMPARING A SINGLE COLUMN VALUES BY ITSELF:
=================================================
Q: WAQ TO DISPLAY EMPLOYEE WHOSE SALARY IS SAME AS THE SALARY OF THE EMPLOYEE FORD?
SELECT E1.ENAME,E1.SAL
FROM EMP E1,EMP E2
WHERE E1.SAL=E2.SAL
AND E2.ENAME='FORD';
EX.ON COMPARING TWO DIFF.COLUMNS TO EACH OTHER:
================================================
EX1:
****
WAQ TO DISPLAY MANAGERS AND THEIR EMPLOYEES FROM EMP TABLE?
SELECT E.EMPNO,E.ENAME,M.EMPNO MGR_NO,M.ENAME MGR_NAME
FROM EMP E,
EMP M
WHERE 1=1
AND M.EMPNO(+)=E.MGR;
/
SELECT E.EMPNO,E.ENAME,M.EMPNO MGR_NO,M.ENAME MGR_NAME
FROM EMP E,
EMP M
WHERE 1=1
AND e.mgr=m.empno(+);
EX2:
****
WAQ TO DISPLAY EMPLOYEE WHO ARE GETTING MORE THAN THEIR MANAGER SALARY?
SELECT M.ENAME MANAGER,M.SAL MSALARY,E.ENAME EMPLOYEE,E.SAL ESALARY
FROM EMP E,EMP M
WHERE M.EMPNO=E.MGR
AND E.SAL>M.SAL
EX3:
****
WAQ TO DISPLAY EMPLOYEE WHO ARE JOINED BEFORE THEIR MANAGER ?
SELECT M.ENAME MANAGER,M.HIREDATE MDOJ,E.ENAME EMPLOYEE,E.HIREDATE EDOJ
FROM EMP E,EMP M
WHERE M.EMPNO=E.MGR
AND E.HIREDATE<M.HIREDATE;
USING CLAUSE:
==============
- IN ANSI FORMAT JOINS WHENEVER WE JOIN TWO OR MORE THAN TWO TABLES INSTEAD OF "ON" CLAUSE WE CAN USE "USING" CLAUSE ALSO.IT RETURNS
COMMON COLUMN ONLY ONE TIME.
EX:
SQL> SELECT * FROM XX_STUDENT S INNER JOIN XX_COURSE C USING(S.CID);
ERROR at line 2:
ORA-01748: only simple column names allowed here
NOTE: WHEN WE USE "USING" CLAUSE WITH COMMON COLUMN NAME THERE IS NO NEED TO PREFIXED WITH TABLE ALIAS NAME.
EX:
USING(S.CID);-------ERROR
USING(CID);------ALLOWED
EX:
***
SELECT * FROM XX_STUDENT S INNER JOIN XX_COURSE C USING(CID);
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO,DNAME,LOC FROM EMP JOIN DEPT USING (DEPTNO);
/
SELECT *
FROM XX_STUDENT S
INNER JOIN XX_COURSE C
USING (CNO)
WHERE CNAME='ORACLE';
/
HOW TO JOIN MORE THAN TWO TABLES:
==================================
SYNTAX FOR NON-ANSI JOINS:
---------------------------------------------------
SELECT * FROM <TN1>,<TN2>,<TN3>,.........<TN n>
WHERE <CONDITION1> AND <CONDITION2> AND <CONDITON3>..........;
SYNTAX FOR ANSI JOINS:
-------------------------------------------
SELECT * FROM <TN1> <JOIN KEY> <TN2> ON <CONDITION1>
<JOIN KEY> <TN3> ON <CONDITION2> <JOIN KEY> <TN4> ............;
INNER JOIN:
==========
SELECT * FROM XX_STUDENT S INNER JOIN XX_COURSE C ON S.CNO=C.CNO
INNER JOIN REGISTOR R ON C.CNO=R.CNO;
(OR)
SELECT * FROM XX_STUDENT S INNER JOIN XX_COURSE C USING(CNO)
INNER JOIN REGISTOR R USING(CNO);
/
--WRITE A QUERY WITH MULTIPLE TABLES.
/
SELECT INVOICE_NUM
FROM AP_INVOICES_ALL
/
SELECT *
FROM AP_INVOICES_ALL AIA,
AP_INVOICE_LINES_ALL AILA,
AP_SUPPLIERS ASA,
AP_SUPPLIER_SITES_ALL AISA
WHERE 1=1
AND AIA.INVOICE_ID=AILA.INVOICE_ID
AND ASA.VENDOR_ID=AIA.VENDOR_ID
AND ASA.VENDOR_ID=AISA.VENDOR_ID
AND AIA.VENDOR_SITE_ID=AISA.VENDOR_SITE_ID
AND AIA.INVOICE_NUM='19879-781';
/
select * from ap_invoice_lines_all aila,
ap_invoices_all aia,
ap_invoice_distributions_all aida
where aila.INVOICE_ID=aia.INVOICE_ID
and aia.INVOICE_ID=aida.INVOICE_ID
AND AILA.LINE_NUMBER(+)=AIDA.DISTRIBUTION_LINE_NUMBER;
/
--Equi Join using Multiple TABLES
/
SELECT APS.VENDOR_NAME,APSA.VENDOR_SITE_CODE,HOU.NAME
FROM AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL APSA,
HR_OPERATING_UNITS HOU
WHERE APS.VENDOR_ID=APSA.VENDOR_ID
AND APSA.ORG_ID=HOU.ORGANIZATION_ID
AND APS.VENDOR_NAME='GE Plastics'
AND APSA.VENDOR_SITE_CODE='GE PLASTICS RM';
/
--Equi Join using Multiple TABLES
/
SELECT AIA.INVOICE_NUM,
APS.VENDOR_NAME,
AIA.INVOICE_AMOUNT,
ACA.CHECK_NUMBER,
AIA.INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_SUPPLIERS APS,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA
WHERE AIA.INVOICE_ID=AIDA.INVOICE_ID
AND AIA.INVOICE_ID=AIPA.INVOICE_ID
AND AIPA.CHECK_ID=ACA.CHECK_ID
AND APS.VENDOR_ID=AIA.VENDOR_ID
--AND AIA.INVOICE_CURRENCY_CODE='USD'
AND APS.VENDOR_NAME='Office Supplies, Inc.'
AND AIA.INVOICE_NUM='19879-781'
AND AIA.INVOICE_CURRENCY_CODE='USD';
/
SELECT AIA.INVOICE_NUM,
APS.VENDOR_NAME,
AIA.INVOICE_AMOUNT,
ACA.CHECK_NUMBER,
AIA.INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_SUPPLIERS APS,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA
WHERE AIA.INVOICE_ID=AIDA.INVOICE_ID
AND AIA.INVOICE_ID=AIPA.INVOICE_ID
AND AIPA.CHECK_ID=ACA.CHECK_ID
AND APS.VENDOR_ID=AIA.VENDOR_ID
--AND AIA.INVOICE_CURRENCY_CODE='USD'
AND APS.VENDOR_NAME='Office Supplies, Inc.'
AND AIA.INVOICE_NUM='19879-781'
AND AIA.INVOICE_CURRENCY_CODE='USD';
/
-- inner join using multiple tables
/
SELECT AIA.INVOICE_NUM,APS.VENDOR_NAME,ACA.CHECK_NUMBER
FROM AP_INVOICES_ALL AIA
INNER JOIN
AP_SUPPLIERS APS
ON AIA.VENDOR_ID=APS.VENDOR_ID
INNER JOIN
AP_INVOICE_DISTRIBUTIONS_ALL AIDA
ON AIA.INVOICE_ID=AIDA.INVOICE_ID
INNER JOIN
AP_INVOICE_PAYMENTS_ALL AIPA
ON AIPA.INVOICE_ID=AIA.INVOICE_ID
INNER JOIN
AP_CHECKS_ALL ACA
ON AIPA.CHECK_ID=ACA.CHECK_ID
AND AIA.INVOICE_NUM='19879-781';
/
No comments:
Post a Comment