Difference between revisions of "Oracle:Joining tables"

From mi-linux
Jump to navigationJump to search
 
(7 intermediate revisions by the same user not shown)
Line 12: Line 12:
 
!colspan="3"|DEPT
 
!colspan="3"|DEPT
 
|-
 
|-
−
!EMPNO!!ENAME!!JOB!!.....!!DEPTNO!! !!DEPTNO!!DNAME!!......
+
!EMPNO!!ENAME!!JOB!!.....!!DEPTNO!!    !!DEPTNO!!DNAME!!......
 
|-
 
|-
−
|7499||ALLEN||SALES||.....||30|| ||30||SALES||......
+
|7499||ALLEN||SALES||.....||30||    ||30||SALES||......
 
|-
 
|-
 
!colspan="4" | 
 
!colspan="4" | 
−
!colspan="3" |join attributes  
+
!colspan="3" |↑ join attributes ↑
 
!colspan="2" | 
 
!colspan="2" | 
 
|}
 
|}
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]]
 +
* [[Oracle:Recursive_Join|Recursive joins]]
 +
* [[Oracle:Outer_Join|Outer joins]]
  
−
The format when joining tables is:
 
  
−
<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 ==
 
  
 
Return to the [[Oracle_Workbook|Workbook]].
 
Return to the [[Oracle_Workbook|Workbook]].

Latest revision as of 14:54, 4 March 2016

Main Page >> Oracle and SQL >> Workbook >> Joining tables

Joining Tables Introduction

Sometimes information needs to be retrieved from more than one table. The relationships between rows in one table and rows in another are established by the values in certain corresponding columns (foreign key).

For example:

EMP   DEPT
EMPNO ENAME JOB ..... DEPTNO      DEPTNO DNAME ......
7499 ALLEN SALES ..... 30      30 SALES ......
  ↑ join attributes ↑  

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:


Return to the Workbook.

Retrieved from "https://mi-linux.wlv.ac.uk/wiki/index.php?title=Oracle:Joining_tables&oldid=8886"