Displaying Data from Multiple Tables
— Natural Join joins two tables based on the same column name,same values,same datatypes
SELECT EMPNO,ENAME,DEPTNO,DNAME FROM EMP
NATURAL JOIN DEPT
/
— Natural Join with more than two Tables based on the same column name,same values,same datatypes —
CREATE TABLE a(id NUMBER,name VARCHAR2(30))
/
CREATE TABLE b(id NUMBER,place VARCHAR2(30))
/
CREATE TABLE c(id NUMBER,city VARCHAR2(30))
/
INSERT INTO a VALUES(1,’K1′)
/
INSERT INTO b VALUES(1,’T.Nagar’)
/
INSERT INTO c VALUES(1,’Mumbai’)
/
SELECT id,name,place,city
FROM a NATURAL JOIN b NATURAL JOIN c
/
— Natural Join with a WHERE CLAUSE —
SELECT EMPNO,ENAME,DEPTNO,DNAME FROM EMP
NATURAL JOIN DEPT
WHERE DEPTNO = 10
/
— Natural Joins with the USING Clause —
The USING clause is used if several columns share the same name but you dont want to join using all of these common columns
SELECT E.EMPNO,E.ENAME,D.DNAME FROM EMP E
JOIN DEPT D USING (DEPTNO)
/
— Qualifying ambiguous column names using ON clause — (If the column names differ in the tables selected for joining we can go for ON clause)
SELECT EMP.EMPNO,EMP.ENAME,DEPT.DEPTNO,DEPT.DNAME
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
/
Note – Table Aliases can be upto 30 characters in length
— Self Joins using ON Clause but with column names different —
SELECT
E.EMPNO “Employee No.”,
E.ENAME “Employee Name”,
D.ENAME “Manager Name”
FROM
EMP E JOIN EMP D
ON (E.MGR = D.EMPNO)
/
SELECT
E.EMPNO “Employee No.”,
E.ENAME ||’ reports to ‘||
D.ENAME “Manager Name”
FROM
EMP E JOIN EMP D
ON (E.MGR = D.EMPNO)
/
— Non Equi Join (Something other than the Equality Operator ‘=’)
SELECT E.EMPNO,E.ENAME,E.SAL,S.GRADE
FROM EMP E JOIN SALGRADE S
ON E.SAL
BETWEEN S.LOSAL AND S.HISAL
/
— OUTER Join (LEFT OUTER,RIGHT OUTER,FULL OUTER) —
LEFT OUTER JOIN
SELECT E.EMPNO,E.ENAME,D.DNAME,D.DEPTNO
FROM emp E LEFT OUTER JOIN dept D
ON (E.deptno = D.deptno)
/
SELECT E.EMPNO,E.ENAME,D.DNAME,D.DEPTNO
FROM dept d LEFT OUTER JOIN emp e
ON (E.deptno = D.deptno)
/
RIGHT OUTER JOIN
SELECT E.EMPNO,E.ENAME,D.DNAME,D.DEPTNO
FROM EMP E RIGHT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
/
— FULL OUTER JOIN — (Unmatched from both the Tables)
CREATE TABLE AA(ID NUMBER,NAME VARCHAR2(50))
/
CREATE TABLE BB(ID NUMBER,PLACE VARCHAR2(50))
/
INSERT INTO AA VALUES(10,’H’)
/
INSERT INTO BB VALUES(10,’MKT’)
/
INSERT INTO AA VALUES(20,’G’)
/
INSERT INTO BB VALUES(20,’SALES’)
/
INSERT INTO AA VALUES(30,’D’)
/
INSERT INTO BB VALUES(50,’AC’)
/
SELECT * FROM AA
/
SELECT * FROM BB
/
SELECT E.ID,E.NAME,D.PLACE,D.ID
FROM AA E FULL OUTER JOIN BB D
ON (E.ID = D.ID)
/
— CARTESIAN PRODUCT —
SELECT E.EMPNO,D.DNAME
FROM EMP E,DEPT D
/
One reason to use a Cartesian join is to generate a large amount of rows to use for testing.
I can take a large table and cross join it to another large table and produce a very large results set.
— Cross Join (Cross Product) —
SELECT ENAME,DNAME
FROM EMP
CROSS JOIN DEPT
/
SELECT ENAME,DNAME
FROM EMP
CROSS JOIN DEPT CROSS JOIN SALGRADE
/
SELECT EMP.EMPNO,EMP.ENAME,DEPT.DNAME,DEPT.DEPTNO
FROM EMP,DEPT
WHERE
EMP.DEPTNO = DEPT.DEPTNO
/
SELECT E.EMPNO,E.ENAME,D.DNAME,D.DEPTNO
FROM EMP E,DEPT D
WHERE
E.DEPTNO = D.DEPTNO
/
SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DEPTNO,D.DNAME
FROM EMP E,DEPT D
WHERE
E.DEPTNO(+)=D.DEPTNO
/
SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DEPTNO,D.DNAME
FROM EMP E,DEPT D
WHERE
E.DEPTNO=D.DEPTNO(+)
/
SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DEPTNO,D.DNAME
FROM EMP E,DEPT D
WHERE
E.DEPTNO(+)=D.DEPTNO(+)
/