Oracle JOINS

• Join is a query that combines rows from two or more table or views.
• Join is performed whenever multiple tables appear in the FROM clause.
• The common column name with in the table should qualify all references.

Join Condition:

• Many Join queries contain WHERE clause , which compares two columns, each column from different table.
• The column in the join condition need not be part of the SELECT list.
• The LOB Columns cannot be specified in the WHERE clause, when the where clause contains any join.

Guidelines:

• To join n tables together, we need minimum of [n-1] join condition.
• If same column name appears in more than one table , the column name must be prefix with the table name.

Qualifying Ambiguous Column Names:

• The name of the column should be Qualified in the WHERE clause with the table name to avoid the Ambiguous.
• If there is no common column name between two tables than Qualified is not necessary but it is better.

Example:

SELECT ENP.ENAME,EMP.EMPNO,DEPT.DEPTNO
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

Table Aliases:

• Table Alias is nothing but alternate name of table.
• Table Alias is specified in the FORM clause.

Example:

SELECT E.ENAME,E.EMPNO,D.DEPTNO
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

Guidelines:

• Table alias max length is 30 characters.
• A table alias should be meaningful and should be maintained as short as possible.
• Table Alias is valid only for that current SELECT statement.

Columns Alias:

• Temporary name of a column.
• Column alias used to make column names more readable.
• Alias is valid only for that current SELECT statement.

Example:

SELECT E.ENAME as Employee_Name,E.EMPNO as EmployeeNumber,D.DEPTNO as Department_Number
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

Type Of Joins:

1. Cartesian Product
2. Simple join or Inner join or Equi join
3. Self join
4. Non Eqie join
5. Outer join

More Practice