Is there any difference in the following two queries?

SELECT EMPNO, ENAME, JOB, SAL, E.DEPTNO, DNAME, LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND SAL>2000; SELECT EMPNO, ENAME, JOB, SAL, E.DEPTNO, DNAME, LOC FROM EMP E, DEPT D WHERE SAL>2000 AND E.DEPTNO=D.DEPTNO;

Cartesian / Cross Products

(rows of one table multiply with rows of another table)

• A Cartesian product is formed when: – A join condition is omitted – A join condition is invalid

– All rows in the first table are joined to all rows in the second table

• To avoid a Cartesian product, always include a valid join condition in a WHERE clause.

Non-Equijoins

A non-equijoin is a join condition containing something other than an equality operator. The relationship between the EMP table and the SALGRADE table has an example of a non- equijoin. A relationship between the two tables is that the SAL column in the EMPL table must be between the values in the LOSAL and HISAL columns of the SALGRADE table. The

relationship is obtained using an operator other than equals (=).

SQL> SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.

SQL> SELECT * FROM SALGRADE; GRADE LOSAL HISAL --- --- --- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

SQL> SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, G.GRADE 2 FROM EMP E, SALGRADE G

3 WHERE E.SAL BETWEEN G.LOSAL AND G.HISAL; EMPNO ENAME JOB SAL GRADE --- --- --- --- --- 7369 SMITH CLERK 800 1 7900 JAMES CLERK 950 1 7876 ADAMS CLERK 1100 1 7521 WARD SALESMAN 1250 2 7654 MARTIN SALESMAN 1250 2 7934 MILLER CLERK 1300 2 7844 TURNER SALESMAN 1500 3 7499 ALLEN SALESMAN 1600 3 7782 CLARK MANAGER 2450 4 7698 BLAKE MANAGER 2850 4 7566 JONES MANAGER 2975 4 7788 SCOTT ANALYST 3000 4 7902 FORD ANALYST 3000 4 7839 KING PRESIDENT 5000 5 14 rows selected.

Non-Equijoins (continued)

The example creates a non-equijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges.

It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list. There are two reasons for this:

• None of the rows in the job grade table contain grades that overlap. That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in the salary grade table.

• All of the employees’ salaries lie within the limits provided by the salgrade table. That is, no employee earns less than the lowest value contained in the LOSAL column or more than the highest value contained in the HISAL column.

Note: Other conditions, such as <= and >= can be used, but BETWEEN is the simplest. Remember to specify the low value first and the high value last when using BETWEEN.

Table aliases have been specified in the slide example for performance reasons, not because of possible ambiguity.

More examples of Non-Equijoins SQL> SELECT * FROM STD_MARKS; RNO CID PER --- --- --- 1 1 75.32 2 1 61.16 3 1 48.95 4 1 89.91 SQL> SELECT * FROM STD_GRADE; GR MIN_PER MAX_PER -- --- --- A1 90 100 A 80 89.99 B 70 79.99 C 60 69.99 D 50 59.99 F 0 49.99 6 rows selected.

SQL> SELECT RNO, PER, GRADE 2 FROM STD_MARKS, STD_GRADE

3 WHERE PER BETWEEN MIN_PER AND MAX_PER 4 ORDER BY 1; RNO PER GR --- --- -- 1 75.32 B 2 61.16 C 3 48.95 F 4 89.91 A SQL>

Outer Joins

Returning Records with No Direct Match with Outer Joins

If a row does not satisfy a join condition, the row will not appear in the query result. For example, in the equijoin condition of EMP and DEPT tables, employee ASLAM does not appear because there is no department number recorded for his in the EMP table. Instead of seeing 15 employees in the result set, you see 14 records.

SQL> SELECT * FROM EMP;

15 rows selected.

SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC

--- --- --- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO

40 OPERATIONS BOSTON No Employee is working in DEPTNO 40 SQL> SELECT EMPNO, ENAME, JOB, SAL, EMP.DEPTNO, DNAME, LOC

2 FROM EMP, DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

--- --- --- --- --- --- --- 7369 SMITH CLERK 800 20 RESEARCH DALLAS

7499 ALLEN SALESMAN 1600 30 SALES CHICAGO 7521 WARD SALESMAN 1250 30 SALES CHICAGO 7566 JONES MANAGER 2975 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 1250 30 SALES CHICAGO 7698 BLAKE MANAGER 2850 30 SALES CHICAGO 7782 CLARK MANAGER 2450 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 3000 20 RESEARCH DALLAS 7839 KING PRESIDENT 5000 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 1500 30 SALES CHICAGO 7876 ADAMS CLERK 1100 20 RESEARCH DALLAS 7900 JAMES CLERK 950 30 SALES CHICAGO 7902 FORD ANALYST 3000 20 RESEARCH DALLAS 7934 MILLER CLERK 1300 10 ACCOUNTING NEW YORK 14 rows selected.

When joining EMP and DEPT tables:

ASLAM’s record is not displaying from EMP table Operations’s record is not displaying from DEPT table

Aslam does not belong to any department because there is a null value in DEPTNO column. So when you join EMP and DEPT tables using equijoin, the values of DEPTNO column of EMP table matches with the values of DEPTNO column of DEPT table, and only those records will be displayed which values matches.

There is an Opeations’s department, DEPTNO 40 in DEPT table, but No Employee is working in DEPTNO 40 in EMP table, so value is not matching. Only those records will by displayed using equijoin which values matches in both tables.

Why we use Outer Joins?

We use outer join when mismatched values to be displayed. or Using Outer Joins to Return Records with No Direct Match

The missing rows can be returned if an outer join operator is used in the join condition. The operator is a plus sign enclosed in parentheses (+), and it is placed on the “side” of the join that is deficient in information. This operator has the effect of creating one or more null rows, to which one or more rows from the nondeficient table can be joined.

In the syntax:

table1.column = is the condition that joins (or relates) the tables together.

table2.column (+) is the outer join symbol, which can be placed on either side of the WHERE clause condition, but not on both sides.

(Place the outer join symbol following the name of the column in the table without the matching rows.)

SQL> SELECT EMPNO, ENAME, JOB, SAL, EMP.DEPTNO, DNAME, LOC 2 FROM EMP, DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO(+);

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

--- --- --- --- --- --- --- 7369 SMITH CLERK 800 20 RESEARCH DALLAS

15 rows selected.

Placed (+) on the “side” of the join that is deficient (missing) in information. Aslam’s record exists in EMP table, but NULL DEPTNO does not exists in DEPT table, so put (+) on DEPT side because DEPT side is missing information.

SQL> SELECT EMPNO, ENAME, JOB, SAL, EMP.DEPTNO, DNAME, LOC 2 FROM EMP, DEPT

3 WHERE EMP.DEPTNO(+)=DEPT.DEPTNO;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

--- --- --- --- --- --- --- 7782 CLARK MANAGER 2450 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 1300 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 5000 10 ACCOUNTING NEW YORK 7566 JONES MANAGER 2975 20 RESEARCH DALLAS 7369 SMITH CLERK 800 20 RESEARCH DALLAS 7788 SCOTT ANALYST 3000 20 RESEARCH DALLAS 7902 FORD ANALYST 3000 20 RESEARCH DALLAS 7876 ADAMS CLERK 1100 20 RESEARCH DALLAS 7521 WARD SALESMAN 1250 30 SALES CHICAGO 7844 TURNER SALESMAN 1500 30 SALES CHICAGO 7499 ALLEN SALESMAN 1600 30 SALES CHICAGO 7900 JAMES CLERK 950 30 SALES CHICAGO 7654 MARTIN SALESMAN 1250 30 SALES CHICAGO 7698 BLAKE MANAGER 2850 30 SALES CHICAGO OPERATIONS BOSTON 15 rows selected.

Placed (+) on the “side” of the join that is deficient (missing) in information. Operations

department DEPTNO 40 exists in DEPT table, but DEPTNO 40 is not assigned to any employee, so put (+) on EMP side because EMPT side is missing information.

In document Oracle SQL.pdf (Page 74-79)