Difference between revisions of "Oracle:Joining tables"
From mi-linux
Jump to navigationJump to search| (12 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | [[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> Joining tables | ||
| + | |||
== Joining Tables Introduction == | == Joining Tables Introduction == | ||
| Line 7: | Line 9: | ||
{| class = "wikitable" | {| class = "wikitable" | ||
!colspan="5"|EMP | !colspan="5"|EMP | ||
| + | ! | ||
!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=" | + | !colspan="3" |↑ join attributes ↑ |
!colspan="2" | | !colspan="2" | | ||
|} | |} | ||
| Line 20: | 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: | ||
| − | + | * [[Oracle:Basic_Join|Basic Join conditions]] | |
| − | + | * [[Oracle:Recursive_Join|Recursive joins]] | |
| − | + | * [[Oracle:Outer_Join|Outer joins]] | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | ---- | |
| − | + | 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.
