Oracle provides JOIN clause which is one the most flexible join declaration type. If you supply join predicate with JOIN then it acts like an INNER join. If join predicate is not provided then it becomes a CROSS Join.
Let us understand different ways of doing an equi join.
--Correct way, most commonly used SELECT e.Ename, e.Job, e.Sal, d.Deptno, d.Dname, d.Loc FROM Emp e, Dept d WHERE e.Deptno = D.Deptno; --Correct way, most commonly used SELECT e.Ename, e.Job, e.Sal, d.Deptno, d.Dname, d.Loc FROM Emp e INNER JOIN Dept d ON e.Deptno = D.Deptno; --Correct way, less commonly used SELECT e.Ename, e.Job, e.Sal, d.Deptno, d.Dname, d.Loc FROM Emp e JOIN Dept d ON e.Deptno = D.Deptno; --Correct way, less commonly used SELECT e.Ename, e.Job, e.Sal, Deptno, d.Dname, d.Loc FROM Emp e INNER JOIN Dept d USING (Deptno); --Correct way, less commonly used SELECT e.Ename, e.Job, e.Sal, Deptno, d.Dname, d.Loc FROM Emp e JOIN Dept d USING (Deptno); --Not recommended SELECT e.Ename, e.Job, e.Sal, d.Deptno, d.Dname, d.Loc FROM Emp e CROSS JOIN Dept d WHERE e.Deptno = D.Deptno; --Rarely used SELECT E.Ename, E.Job, E.Sal, Deptno, D.Dname, D.Loc FROM Emp E NATURAL JOIN Dept D; ENAME JOB SAL DEPTNO DNAME LOC ---------- --------- ---------- ---------- -------------- ------------- CLARK MANAGER 2450 10 ACCOUNTING NEW YORK MILLER CLERK 1300 10 ACCOUNTING NEW YORK KING PRESIDENT 5000 10 ACCOUNTING NEW YORK FORD ANALYST 3000 20 RESEARCH DALLAS SCOTT ANALYST 3000 20 RESEARCH DALLAS JONES MANAGER 2975 20 RESEARCH DALLAS SMITH CLERK 800 20 RESEARCH DALLAS ADAMS CLERK 1100 20 RESEARCH DALLAS WARD SALESMAN 1250 30 SALES CHICAGO MARTIN SALESMAN 1250 30 SALES CHICAGO TURNER SALESMAN 1500 30 SALES CHICAGO JAMES CLERK 950 30 SALES CHICAGO ALLEN SALESMAN 1600 30 SALES CHICAGO BLAKE MANAGER 2850 30 SALES CHICAGO 14 rows selected.
ANSI Joins are simpler for understanding while performing joins with more than two tables as compared with Oracle’s proprietary joins why because
- ANSI joins are keyword oriented and hence easy to track the join used and operated.
- Multiple joins are performed with the result of the previous join.
SELECT E.Ename, E.Job, E.Sal, Deptno, D.Dname, D.Loc
FROM Emp E
INNER JOIN Dept D
USING(Deptno)
JOIN SalGrade S
ON E.Sal BETWEEN S.LoSal AND S.HiSal;
ENAME JOB SAL DEPTNO DNAME LOC
---------- --------- ---------- ---------- -------------- -------------
CLARK MANAGER 2450 10 ACCOUNTING NEW YORK
MILLER CLERK 1300 10 ACCOUNTING NEW YORK
KING PRESIDENT 5000 10 ACCOUNTING NEW YORK
FORD ANALYST 3000 20 RESEARCH DALLAS
SCOTT ANALYST 3000 20 RESEARCH DALLAS
JONES MANAGER 2975 20 RESEARCH DALLAS
SMITH CLERK 800 20 RESEARCH DALLAS
ADAMS CLERK 1100 20 RESEARCH DALLAS
WARD SALESMAN 1250 30 SALES CHICAGO
MARTIN SALESMAN 1250 30 SALES CHICAGO
TURNER SALESMAN 1500 30 SALES CHICAGO
JAMES CLERK 950 30 SALES CHICAGO
ALLEN SALESMAN 1600 30 SALES CHICAGO
BLAKE MANAGER 2850 30 SALES CHICAGO
14 rows selected.
Brackets in the from clause can change the syntax of the join condition and can confuse a little. Please have a look at the below.
SELECT e1.Ename, e1.Sal, e1.Deptno deptno_e1, d1.Deptno deptno_d1, d2.Deptno deptno_d2, d3.Deptno deptno_d3
FROM ( emp e1
INNER JOIN ( dept d1
INNER JOIN ( dept d2 INNER JOIN dept d3 ON d2.deptno = d3.deptno )
ON d1.deptno = d3.deptno
)
ON e1.deptno = d3.deptno
);
ENAME SAL DEPTNO_E1 DEPTNO_D1 DEPTNO_D2 DEPTNO_D3
---------- ---------- ---------- ---------- ---------- ----------
SMITH 800 20 20 20 20
ALLEN 1600 30 30 30 30
WARD 1250 30 30 30 30
JONES 2975 20 20 20 20
MARTIN 1250 30 30 30 30
BLAKE 2850 30 30 30 30
CLARK 2450 10 10 10 10
SCOTT 3000 20 20 20 20
KING 5000 10 10 10 10
TURNER 1500 30 30 30 30
ADAMS 1100 20 20 20 20
JAMES 950 30 30 30 30
FORD 3000 20 20 20 20
MILLER 1300 10 10 10 10
14 rows selected.
View the table structures and answer below questions. You can download the table DDLs and insert statements here.
SELECT * FROM Employee; ENAME CITY -------------------- -------------------- ANIL MUMBAI SHANKAR PUNE JAYA CHENNAI SUNIL PUNE VIJAY DELHI PRAKASH CALCUTTA AJAY CHENNAI 7 rows selected. SELECT * FROM Emp_Details; ENAME CNAME SALARY JOINDATE -------------------- -------------------- ---------- --------- ANIL ACC 1500 01-MAY-89 SHANKAR TATA 2000 10-JUL-90 JAYA CMC 1800 07-JUN-91 SUNIL CMC 1700 01-JAN-89 VIJAY TATA 5000 03-JAN-88 PRASASH TATA 3000 27-MAY-89 AJAY ACC 8000 30-APR-95 AMOL ACC 1000 17-MAR-95 8 rows selected. SELECT * FROM Company; CNAME CITY -------------------- -------------------- ACC CHENNAI TATA PUNE ACC MUMBAI CMC PUNE CMC CHENNAI TATA DELHI 6 rows selected. SELECT * FROM Manager; ENAME MNAME -------------------- -------------------- ANIL AJAY SHANKAR VIJAY JAYA SUNIL JAYA VIJAY PRAKASH SHANKAR AJAY 7 rows selected. SELECT * FROM Emp_Shift; ENAME SHIFT -------------------- ----- ANIL A SUNIL B VIJAY B PRAKASH C
Q1 : Display the details of the employees living in city “PUNE” and working at “DELHI”?
Ans:
SELECT EC.Ename, EC.Cname Company, EC.Salary, EC.JOINDATE, C.City Comp_City, E.City Emp_City FROM Emp_Details EC, Company C, Employee E WHERE EC.Cname = C.Cname AND EC.Ename = E.Ename AND E.City = 'PUNE' AND C.City = 'DELHI'; ENAME COMPANY SALARY JOINDATE COMP_CITY EMP_CITY ---------- ---------- ---------- --------- --------------- ---------- SHANKAR TATA 2000 10-JUL-90 DELHI PUNE
Q2 : Display the details of the employees living in the same city where their companies are located?
Ans:
SELECT EC.Ename, EC.Cname Company, EC.Salary, EC.JOINDATE, C.City Comp_City, E.City Emp_City 2 FROM Emp_Details EC, Company C, Employee E 3 WHERE EC.Cname = C.Cname AND EC.Ename = E.Ename AND C.City = E.City; ENAME COMPANY SALARY JOINDATE COMP_CITY EMP_CITY ---------- ---------- ---------- --------- --------------- ---------- AJAY ACC 8000 30-APR-95 CHENNAI CHENNAI SHANKAR TATA 2000 10-JUL-90 PUNE PUNE ANIL ACC 1500 01-MAY-89 MUMBAI MUMBAI SUNIL CMC 1700 01-JAN-89 PUNE PUNE JAYA CMC 1800 07-JUN-91 CHENNAI CHENNAI VIJAY TATA 5000 03-JAN-88 DELHI DELHI 6 rows selected.
Q3 : Display the list of employees living in the city where “Sunil” is living?
Ans:
SELECT EC.Ename, EC.Cname, EC.Salary, EC.JoinDate, E.City Emp_City FROM Emp_Details EC, Employee E, Employee C WHERE EC.Ename = E.Ename AND C.Ename = 'SUNIL' AND E.City = C.City; ENAME CNAME SALARY JOINDATE EMP_CITY ---------- -------------------- ---------- --------- ---------------- SHANKAR TATA 2000 10-JUL-90 PUNE SUNIL CMC 1700 01-JAN-89 PUNE
Q : Display the names of employees having same company city as “Sunil”?
Ans:
SELECT EC.Ename, EC.Cname, EC.Salary, EC.JoinDate, C.City Emp_City FROM Emp_Details EC, Company C, Emp_Details EComp, Company Comp WHERE EC.Cname = C.Cname AND ( EComp.Cname = Comp.Cname AND EComp.Ename = 'SUNIL' ) AND C.City = Comp.City; ENAME CNAME SALARY JOINDATE EMP_CITY ---------- -------------------- ---------- --------- -------------- ANIL ACC 1500 01-MAY-89 CHENNAI SHANKAR TATA 2000 10-JUL-90 PUNE JAYA CMC 1800 07-JUN-91 CHENNAI JAYA CMC 1800 07-JUN-91 PUNE SUNIL CMC 1700 01-JAN-89 CHENNAI SUNIL CMC 1700 01-JAN-89 PUNE VIJAY TATA 5000 03-JAN-88 PUNE PRASASH TATA 3000 27-MAY-89 PUNE AJAY ACC 8000 30-APR-95 CHENNAI AMOL ACC 1000 17-MAR-95 CHENNAI 10 rows selected.