Table Join Conditions
This section covers getting data from more than one table at a time (joins). Joins are used to combine columns from two or more tables. Figure 7.16 shows a simple join between EMP and DEPT. Notice that the table name is specified with each duplicate name of columns between the two tables (see the WHERE clause of Figure 7.16). Figure 7.17 shows the same query but using a table name 'alias.' This alias can be used anyplace that further qualification is necessary (columns with the same name in different tables).
Figure 7.16. Simple table join example.
Figure 7.17. Simple table join with table name qualification example.
Figure 7.18 shows what happens if you forget the WHERE clause joining a condition between the tables. Oracle9i will return all the rows from one table for each row in the second table. This is known as a Cartesian Product and is usually NOT the desired result.
Figure 7.18. Cartesian product example.
An outer join is useful to show all possible conditions, even when there are no matches from one of the tables. Figure 7.19 shows the outer join syntax. Notice the '(+) syntax on' in the WHERE clause. This tells Oracle to return rows from the join whether there is a condition match or not.
Figure 7.19. Outer join example.
Self joins are like a join between two tables except that the conditions are against two different columns of the same table. Qualifying the column names would be necessary. The EMP table has an employee id (empno) and a manager (mgr), which is also an empno. Figure 7.20 shows a query that displays the employees and their associated managers. Notice the outer join condition to show that the PRESIDENT does not have a manager.
Figure 7.20. Self join example.
Whenever there is not an equal condition between two or more tables in a join condition, it is referred to as a non-equijoin. Figure 7.21 shows how this works. Notice that there is a condition in the WHERE clause that will return only one row from the SALGRADE table. This relationship is essential or you will get a Cartesian Product. Oracle9i will still return all the rows that match the condition from EMP for each row returned from SALGRADE.
Figure 7.21. NON-Equijoin example.
There is a method of combining the result sets of several queries into one result set. You will learn later in this chapter with SQL*Plus Reporting a few other uses for this feature. The UNION operator combines two or more result sets together and removes any duplicate rows. The INTERSECT operator returns rows that two or more queries have in common, and the MINUS operator returns just the rows between two tables that are not in the other.
To work the examples, you will need to create three new tables (based on the EMP table), as illustrated in Figure 7.22.
Figure 7.22. Creating example tables.
The rule for using this technique is that each query MUST return the same number of columns and the same data types for each column. Figure 7.23 illustrates the UNION command. Notice that you can include an ORDER BY clause and that this clause uses the position of the column in the sort order.
Figure 7.23. UNION operator example.
The INTERSECT operator will show rows in common between two or more tables. The MINUS operator will show rows that do not appear in two or more tables. Figure 7.24 shows the INTERSECT and MINUS operators in use.
Figure 7.24. INTERSECT and MINUS operator example.
