|
|
| Line 23: |
Line 23: |
| | The table must have matching values in the ''join attributes'' to enable a join to take place. If there are no matching values, the tables will not join! For example, if 30 was missing from the DEPT table, then the employees from department 30 would not appear in any output that joins tables DEPT and EMP together. (A consequence of violating referential integrity!) | | The table must have matching values in the ''join attributes'' to enable a join to take place. If there are no matching values, the tables will not join! For example, if 30 was missing from the DEPT table, then the employees from department 30 would not appear in any output that joins tables DEPT and EMP together. (A consequence of violating referential integrity!) |
| | | | |
| | + | There are different ways to join tables: |
| | | | |
| − | === Basic SQL Structure For Joining Tables ===
| + | * [[Oracle:Basic_Join|Basic Join conditions]] |
| − | | + | * Recursive joins |
| − | The format when joining tables is:
| + | * Outer joins |
| − | | |
| − | <code>
| |
| − | SELECT some columns
| |
| − | | |
| − | FROM two or more tables
| |
| − | | |
| − | WHERE table1.col1 = table2.col2 [ AND table3.col3 = table4.col4 [AND ....]]
| |
| − | </code>
| |
| − | | |
| − | Note, two, or more tables can be joined in a SQL statement, but each join condition specifies the link between two tables only. If, for example, three tables appear in the FROM clause, there should normally be two join conditions.
| |
| − | | |
| − | | |
| − | === Join Operators ===
| |
| − | | |
| − | {| class="wikitable"
| |
| − | !Operator||Description
| |
| − | |-
| |
| − | |=||Equal
| |
| − | |-
| |
| − | |<>||Not equal
| |
| − | |-
| |
| − | |>||Greater than
| |
| − | |-
| |
| − | |>=||Greater than, or equal to
| |
| − | |-
| |
| − | |<||Less than
| |
| − | |-
| |
| − | |<=||Less than, or equal to
| |
| − | |-
| |
| − | |BETWEEN lower-value AND higher-value||A value between lower and higher
| |
| − | |-
| |
| − | |LIKE||Pattern matching
| |
| − | |}
| |
| − | | |
| − | === Example Join Queries ===
| |
| − | | |
| − | To find ALLEN's location:
| |
| − | | |
| − | SELECT ENAME, LOC
| |
| − | FROM EMP E, DEPT D
| |
| − | WHERE ENAME = 'ALLEN' AND E.DEPTNO = D.DEPTNO;
| |
| − | | |
| − | Note: when two columns from different tables have the same name (i.e., DEPTNO in this case), you must use the table name prefixes to clarify exactly which columns you mean. Here, aliases have been used for the table names, e.g., E represents EMP and D DEPT. In this case the aliases work as a shorthand notation for the relevant tables. In other cases, for example, when comparing a table to itself (see later), they are vital.
| |
| − | | |
| − | To list information about all the employees in Chicago (without aliases):
| |
| − | | |
| − | SELECT DEPT.DEPTNO, DNAME, LOC, ENAME, JOB
| |
| − | FROM EMP, DEPT
| |
| − | WHERE EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'CHICAGO';
| |
| − | | |
| − | The same query using table abbreviations/aliases:
| |
| − | | |
| − | SELECT D.*, ENAME, JOB
| |
| − | FROM EMP E, DEPT D
| |
| − | WHERE E.DEPTNO = D.DEPTNO AND LOC = 'CHICAGO';
| |
| − | | |
| − | Note that the abbreviations are defined in the FROM clause. Once defined they must be used in place of the table they are representing.
| |
| − | | |
| − | Columns can also be given aliases with the <code>AS new_column_name</code> clause. This should directly follow the column name in the SELECT clause. The new column name can not contains spaces, unless you use double quotes and can be a maximum of 10 characters.
| |
| − | | |
| − | Joining DEPT and EMP using table and column aliases:
| |
| − | | |
| − | SELECT D.DEPTNO, DNAME AS DEPT_NAME, JOB, ENAME AS "EMP Name"
| |
| − | FROM DEPT D, EMP E
| |
| − | WHERE D.DEPTNO = E.DEPTNO
| |
| − | ORDER BY D.DEPTNO;
| |
| − | | |
| − | To find the salary grade of each employee:
| |
| − |
| |
| − | SELECT GRADE, JOB, ENAME, SAL
| |
| − | FROM SALGRADE, EMP
| |
| − | WHERE SAL BETWEEN LOSAL AND HISAL
| |
| − | ORDER BY GRADE, JOB;
| |
| − | | |
| − | This is an example of a BETWEEN join, where the SAL field of each row of the EMP table is "tested" against the LOSAL and HISAL values of each row of the SALGRADE table to find the associated salary grade.
| |
| − | | |
| − | {|class = "wikitable"
| |
| − | |+SALGRADE
| |
| − | |-
| |
| − | !GRADE!!LOSAL!!HISAL
| |
| − | |-
| |
| − | |1||700||1200
| |
| − | |-
| |
| − | |2||1201||1400
| |
| − | |-
| |
| − | |3||1401||2000
| |
| − | |}
| |
| − | | |
| − | In SQL, a column cannot be compared to itself, however for some queries this may be necessary, e.g., it may be required to compare one salesman's commission against another. To achieve this table aliases must be used.
| |
| − | | |
| − | To find the name and department of salesmen who earn more commission than 'Ward':
| |
| − | | |
| − | SELECT S.ENAME, S.DEPTNO, S.COMM, J.ENAME, J.DEPTNO, J.COMM
| |
| − | FROM EMP S, EMP J
| |
| − | WHERE J.ENAME = 'WARD'
| |
| − | AND S.JOB = 'SALESMAN'
| |
| − | AND S.COMM > J.COMM;
| |
| − | | |
| − | To find the salary and jobs of employees who earn more than Jones:
| |
| − | | |
| − | SELECT X.ENAME, X.SAL, X.JOB, Y.ENAME, Y.SAL, Y.JOB
| |
| − | FROM EMP X, EMP Y
| |
| − | WHERE X.SAL > Y.SAL AND Y.ENAME = 'JONES';
| |
| − | | |
| − | The query should result in something similar to:
| |
| − | | |
| − | <pre style="color: blue">
| |
| − | ENAME SAL JOB ENAME SAL JOB
| |
| − | ---------- --------- --------- ---------- --------- ---------
| |
| − | KING 5000 PRESIDENT JONES 2975 MANAGER
| |
| − | SCOTT 3000 ANALYST JONES 2975 MANAGER
| |
| − | FORD 3000 ANALYST JONES 2975 MANAGER
| |
| − | </pre>
| |
| − | | |
| | | | |
| | == Next Step == | | == Next Step == |
| | | | |
| | Return to the [[Oracle_Workbook|Workbook]]. | | Return to the [[Oracle_Workbook|Workbook]]. |
