This week we will learn Working with multiple Tables which covers : Joins - Inner Join; Using Table Aliases; Outer Join; Self Join; Non-Equi Join Subqueries - Simple Subqueries; Multilevel Subqueries; Correlated Subqueries; Predicates
Set Operators - Set Operator Examples

Aliases:
An alias in oracle is another name used for a column or a table inside SELECT query. They are accessible only inside the query where they are created. Once the query execution is over alias will perish. An alias can be a single word or can be more than one word. If alias used is more than one word it must be enclosed in double quotes

example:
current_date or "current date"
Student_name or "Student name"
Aliases are used in places when ever there is multiple usage of longer table names.
example :
STUDENT_MASTER_RECORD can be aliased as SMR
ACCOUNT_MASTER_TABLE as AMT .

There are 2 types of aliases
i) Table alias : It is another name used to refer the table within the
query.
example :
SQL> SELECT ENAME , SAL FROM EMP E WHERE E.DEPTNO = 10;
In the above query "E" is an alias for EMP.

ii) column alias
SQL> SELECT SYSDATE "Current Date" FROM DUAL;
"Current Date" is an alias.

Working Wih Multiple Tables

To retrieve desired data from more than one table we use join.
We will have more than one table name after FROM key word in
SELECT statement. A SELECT statement on multiple tables without
a proper JOIN condition will lead to a cartesian product.(i.e.
No.Of Output rows = No.of rows in table1 X No of rows in table2....)

No of join conditions = No of tables joined - 1

Types Of Joins:

1) EQUI JOIN: The equi join is normally used to join tables with primary key foreign key relation ships.

Example :
SQL> SELECT ENAME,JOB,DEPTNO FROM EMP;

ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
ALLEN SALESMAN 30
WARD SALESMAN 30
JONES MANAGER 20
MARTIN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
TURNER SALESMAN 30
ADAMS CLERK 20
JAMES CLERK 30
FORD ANALYST 20
MILLER CLERK 10
-------------------------------------

SQL> SELECT DEPTNO,DNAME,LOC FROM DEPT;

DEPTNO DNAME LOC ---- ----------- -------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
To display ENAME of EMP table ,DNAME of DEPT table we need to join those tables as shown below.

SQL> SELECT E.DEPTNO , ENAME , DNAME FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO;

DEPTNO ENAME DNAME
-------- ---------- --------------

10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALES

In the above query "E" and "D" are the alias names for the tables EMP
and DEPT respectively. As column DEPTNO is there in both the tables, table
name is used along with column name DEPTNO. This is to avoid the ambiguity

2) NON-EQUI JOIN :
A join condition where any relation operator other than "=" equal to
operator is used.

Consider the following examples Using SALGRADE and EMP tables.

SQL> SELECT * FROM SALGRADE;

GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> SELECT ENAME,SAL, GRADE FROM EMP E,SALGRADE S
WHERE SAL BETWEEN LOSAL AND HISAL

ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5

3) OUTER JOIN :
In EQUI JOIN rows that does not satisfy specified condition would not be displayed. Example: DEPTNO 40 is not displayed in the previous example
because there are no employees in it. If we want to diplay its detail also
then we have to use OUTER JOIN.Otherwise OUTER JOIN is imilar to EQUI JOIN
except for the difference it uses outer join (+) operator. (A plus within
parenthesis) towards the side not having required data. Outer join operator will substitute null values when there are no values available.

SQL> SELECT E.DEPTNO,ENAME,DNAME FROM EMP E , DEPT D
2 WHERE E.DEPTNO (+) = D.DEPTNO;

DEPTNO ENAME DNAME
---------- ---------- --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALES
OPERATIONS

SQL>SELECT D.DEPTNO,ENAME,DNAME FROM EMP E , DEPT D
WHERE E.DEPTNO (+) = D.DEPTNO

DEPTNO ENAME DNAME
---------- ---------- --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALES
40 OPERATIONS

Next Page: Tutorial 5: Working with Multiple Tables - Page 2