Difference between revisions of "Oracle:Joining tables"
| Line 14: | Line 14: | ||
|- | |- | ||
!colspan="4" | | !colspan="4" | | ||
| − | !colspan | + | !colspan="2" |join using these columns |
!colspan="2" | | !colspan="2" | | ||
| − | |||
| − | |||
|} | |} | ||
| Line 29: | Line 27: | ||
<code> | <code> | ||
| − | SELECT some columns | + | SELECT some columns</br> |
| − | FROM two or more tables | + | FROM two or more tables</br> |
WHERE table1.col1 = table2.col2 [ AND table3.col3 = table4.col4 [AND ....]] | WHERE table1.col1 = table2.col2 [ AND table3.col3 = table4.col4 [AND ....]] | ||
</code> | </code> | ||
| Line 37: | Line 35: | ||
=== Join Operators === | === Join Operators === | ||
| − | = Equal | + | {! class="wikitable" |
| − | <> Not equal | + | !Operator||Description |
| − | > Greater than | + | |- |
| − | >= Greater than, or equal to | + | |=||Equal |
| − | < Less than | + | |- |
| − | <= Less than, or equal to | + | |<>||Not equal |
| − | BETWEEN | + | |- |
| − | LIKE Pattern matching | + | |>||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 === | === Example Join Queries === | ||
Revision as of 16:33, 2 March 2016
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 using these columns | |||||||
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!)
Basic SQL Structure For Joining Tables
The format when joining tables is:
SELECT some columns
FROM two or more tables
WHERE table1.col1 = table2.col2 [ AND table3.col3 = table4.col4 [AND ....]]
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;
