Joins
A join is a query that combines rows from two or more tables. Oracle performs a join whenever multiple tables appear in the FROM clause of the query. Below is a SQL join statement, legacy syntax and ANSI/ISO syntax (also referred as new join syntax, was first introduced in Oracle 9i release) in a simple pseudo code form.
- ANSI/ISO Syntax:
SELECT * FROM <table one> [join type] JOIN <table two> USING <join columns> | ON <join conditions> [WHERE <where clause filters/restrictions>]
- Legacy Syntax:
SELECT * FROM <table one>, <table two> [WHERE <where clause join conditions>] [<where clause filters/restrictions>]
Join Conditions
Most of the queries will contain at least one join condition, either in the FROM clause or in the WHERE clause. The join condition compares two columns, each from a different table. To execute a join, Oracle database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE.
To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.
Filters (also known as Restrictions)
A WHERE clause can also contain filter conditions that refer to columns of only one table, which can further restrict the rows returned by the join query.
- Let us create some test tables to demonstrate different type of joins
SQL> CREATE TABLE t1
2 AS
3 SELECT * FROM
4 (
5 SELECT rownum-1 id,
6 DECODE(rownum-1,0,'Zero',NULL) word,
7 (rownum-1)*10 t1n,
8 SUBSTR(DECODE(rownum-1,0,'Zero',TO_CHAR(TO_DATE((rownum-1)*10,'J'),'Jsp')),1,12) t1word,
9 (rownum-1)*1000 t1value
10 FROM dual CONNECT BY LEVEL <= 11
11 )
12 WHERE ID IN (0,1,3,5,7,10);
Table created.
SQL> CREATE TABLE t2
2 AS
3 SELECT * FROM
4 (
5 SELECT rownum-1 id,
6 DECODE(rownum-1,0,'Zero',NULL) word,
7 (rownum-1)*100 t2n,
8 SUBSTR(DECODE(rownum-1,0,'Zero',TO_CHAR(TO_DATE((rownum-1)*100,'J'),'Jsp')),1,12) t2word,
9 (rownum-1)*1000 t2value
10 FROM DUAL CONNECT BY LEVEL <= 11
11 )
12 WHERE ID IN (0,1,2,4,6,8,9);
Table created.
SQL> CREATE TABLE t3
2 AS
3 SELECT 'GRP' || LPAD (ROWNUM, 2, '0') group_id,
4 ((ROWNUM - 1) * 2000) + 1 low_value, ROWNUM * 2000 high_value
5 FROM DUAL CONNECT BY LEVEL <= 6;
Table created.
SQL> SELECT * FROM t1;
ID WORD T1N T1WORD T1VALUE
---------- ---- ---------- ------------ ----------
0 Zero 0 Zero 0
1 10 Ten 1000
3 30 Thirty 3000
5 50 Fifty 5000
7 70 Seventy 7000
10 100 One Hundred 10000
6 rows selected.
SQL> SELECT * FROM t2;
ID WORD T2N T2WORD T2VALUE
---------- ---- ---------- ------------ ----------
0 Zero 0 Zero 0
1 100 One Hundred 1000
2 200 Two Hundred 2000
4 400 Four Hundred 4000
6 600 Six Hundred 6000
8 800 Eight Hundre 8000
9 900 Nine Hundred 9000
7 rows selected.
SQL> SELECT * FROM t3;
GROUP LOW_VALUE HIGH_VALUE
----- ---------- ----------
GRP01 1 2000
GRP02 2001 4000
GRP03 4001 6000
GRP04 6001 8000
GRP05 8001 10000
GRP06 10001 12000
6 rows selected.
Join Types
- Cross Joins: If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other.
ANSI/ISO Syntax
SQL> SELECT t1n, t2word, t2n, t2word
2 FROM t1 CROSS JOIN t2;
T1N T2WORD T2N T2WORD
---------- ------------ ---------- ------------
0 Zero 0 Zero
...
100 Nine Hundred 900 Nine Hundred
42 rows selected.
Legacy Syntax
SQL> SELECT t1n, t2word, t2n, t2word
2 FROM t1, t2;
T1N T2WORD T2N T2WORD
---------- ------------ ---------- ------------
0 Zero 0 Zero
...
100 Nine Hundred 900 Nine Hundred
42 rows selected.
- Equi Joins (also known as Natural Joins): An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.
// Using Clause //
ANSI/ISO Syntax
SQL> SELECT id, t1.word, t2.word,t1n, t1word, t2n, t2word
2 FROM t1 JOIN t2 USING(id);
ID WORD WORD T1N T1WORD T2N T2WORD
---------- ---- ---- ---------- ------------ ---------- ------------
0 Zero Zero 0 Zero 0 Zero
1 10 Ten 100 One Hundred
Legacy Syntax
SQL> SELECT t1.id, t1.word, t2.word,t1n, t1word, t2n, t2word
2 FROM t1, t2
3 WHERE t1.id = t2.id;
ID WORD WORD T1N T1WORD T2N T2WORD
---------- ---- ---- ---------- ------------ ---------- ------------
0 Zero Zero 0 Zero 0 Zero
1 10 Ten 100 One Hundred
// Natural Join Clause – performs a join on all columns with the matching names in T1 and T2//
ANSI/ISO Syntax
SQL> SELECT id, word, t1n, t1word, t2n, t2word
2 FROM t1 NATURAL JOIN t2;
ID WORD T1N T1WORD T2N T2WORD
---------- ---- ---------- ------------ ---------- ------------
0 Zero 0 Zero 0 Zero
Legacy Syntax
SQL> SELECT t1.id, t2.word, t1n, t1word, t2n, t2word
2 FROM t1, t2
3 WHERE t1.id = t2.id
4 AND t1.word = t2.word;
ID WORD T1N T1WORD T2N T2WORD
---------- ---- ---------- ------------ ---------- ------------
0 Zero 0 Zero 0 Zero
// ON Clause //
ANSI/ISO Syntax
SQL> SELECT t1.id, t1.word, t1n, t1word, t2n, t2word
2 FROM t1 JOIN t2 ON (t1.t1n = t2.t2n);
ID WORD T1N T1WORD T2N T2WORD
---------- ---- ---------- ------------ ---------- ------------
0 Zero 0 Zero 0 Zero
10 100 One Hundred 100 One Hundred
Legacy Syntax
SQL> SELECT t1.id, t1.word, t1n, t1word, t2n, t2word
2 FROM t1, t2
3 WHERE t1.t1n = t2.t2n;
ID WORD T1N T1WORD T2N T2WORD
---------- ---- ---------- ------------ ---------- ------------
0 Zero 0 Zero 0 Zero
10 100 One Hundred 100 One Hundred
- Theta Joins (also known as Inner Joins): Theta join is technically, nothing but a cross join with a join condition. Simply put, instead of returning all the rows of a cartesian product, only rows matching the join condition are returned.
ANSI/ISO Syntax
SQL> -- Without Inner Keyword
SQL> SELECT t1.id,t1.word,t1.t1value,t3.group_id
2 FROM t1 JOIN t3 ON t1.t1value
3 BETWEEN t3.low_value AND t3.high_value;
ID WORD T1VALUE GROUP
---------- ---- ---------- -----
10 10000 GRP05
7 7000 GRP04
5 5000 GRP03
3 3000 GRP02
1 1000 GRP01
SQL> -- With Inner Keyword
SQL> SELECT t1.id,t1.word,t1.t1value,t3.group_id
2 FROM t1 INNER JOIN t3 ON t1.t1value
3 BETWEEN t3.low_value AND t3.high_value;
ID WORD T1VALUE GROUP
---------- ---- ---------- -----
10 10000 GRP05
7 7000 GRP04
5 5000 GRP03
3 3000 GRP02
1 1000 GRP01
Legacy Syntax
SQL> SELECT t1.id,t1.word,t1.t1value,t3.group_id
2 FROM t1, t3
3 WHERE t1.t1value
4 BETWEEN t3.low_value AND t3.high_value;
ID WORD T1VALUE GROUP
---------- ---- ---------- -----
10 10000 GRP05
7 7000 GRP04
5 5000 GRP03
3 3000 GRP02
1 1000 GRP01
- Outer Joins: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
// To write a query that performs an outer join of tables T1 and T2 and returns all rows from T1 (a left outer join)
ANSI/ISO Syntax (Use the LEFT [OUTER] JOIN syntax in the FROM clause)
SQL> SELECT t1.id, t2.word, t2.id, t2.word
2 FROM t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id);
ID WORD ID WORD
---------- ---- ---------- ----
0 Zero 0 Zero
1 1
7
5
3
10
6 rows selected.
Legacy Syntax (Apply the outer join operator (+) to all columns of T2 in the join condition in the WHERE clause)
SQL> SELECT t1.id, t2.word, t2.id, t2.word
2 FROM t1, t2
3 WHERE t1.id = t2.id(+);
ID WORD ID WORD
---------- ---- ---------- ----
0 Zero 0 Zero
1 1
7
5
3
10
6 rows selected.
// To write a query that performs an outer join of tables T1 and T2 and returns all rows from T2 (a right outer join)
ANSI/ISO Syntax (Use the RIGHT [OUTER] JOIN syntax in the FROM clause)
SQL> SELECT t1.id, t2.word, t2.id, t2.word
2 FROM t1 RIGHT OUTER JOIN t2 ON (t1.id = t2.id);
ID WORD ID WORD
---------- ---- ---------- ----
0 Zero 0 Zero
1 1
8
2
6
4
9
7 rows selected.
Legacy Syntax (Apply the outer join operator (+) to all columns of T1 in the join condition in the WHERE clause)
SQL> SELECT t1.id, t2.word, t2.id, t2.word
2 FROM t1, t2
3 WHERE t1.id (+)= t2.id;
ID WORD ID WORD
---------- ---- ---------- ----
0 Zero 0 Zero
1 1
8
2
6
4
9
7 rows selected.
// To write a query that performs an outer join and returns all rows from T1 and T2, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.
ANSI/ISO Syntax
SQL> SELECT t1.id, t2.word, t2.id, t2.word
2 FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
ID WORD ID WORD
---------- ---- ---------- ----
0 Zero 0 Zero
1 1
7
5
3
10
8
2
6
4
9
11 rows selected.
Legacy Syntax (Not available)
- Self Joins: A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.
ANSI/ISO Syntax
SQL> SELECT a.id, a.word, a.t1n, b.id, b.word, b.t1n
2 FROM t1 a JOIN t1 b ON (a.id = b.t1n);
ID WORD T1N ID WORD T1N
---------- ---- ---------- ---------- ---- ----------
0 Zero 0 0 Zero 0
10 100 1 10
Legacy Syntax
SQL> SELECT a.id, a.word, a.t1n, b.id, b.word, b.t1n
2 FROM t1 a, t1 b
3 WHERE a.id = b.t1n;
ID WORD T1N ID WORD T1N
---------- ---- ---------- ---------- ---- ----------
0 Zero 0 0 Zero 0
10 100 1 10
- Anti-Joins: An antijoin returns rows from the left side of the predicate for which there are no (NOT IN or NOT EXISTS) corresponding rows on the right side of the predicate. Simply put, where rows from one table with no matching rows from other table are returned.
SQL> SELECT id, t1n, t1word
2 FROM t1
3 WHERE id NOT IN (SELECT id FROM t2);
ID T1N T1WORD
---------- ---------- ------------
3 30 Thirty
5 50 Fifty
7 70 Seventy
10 100 One Hundred
SQL> SELECT id, t1n, t1word
2 FROM t1
3 WHERE NOT EXISTS
4 (SELECT id FROM t2 WHERE t2.id = t1.id);
ID T1N T1WORD
---------- ---------- ------------
7 70 Seventy
5 50 Fifty
3 30 Thirty
10 100 One Hundred
- Semi-Joins: A semijoin returns rows that match an IN or EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. Simply put, all rows from table on the left are returned for a matching condition on the right.
SQL> SELECT id, t1n, t1word
2 FROM t1
3 WHERE id IN (SELECT id FROM t2);
ID T1N T1WORD
---------- ---------- ------------
0 0 Zero
1 10 Ten
SQL> SELECT id, t1n, t1word
2 FROM t1
3 WHERE EXISTS
4 (SELECT id FROM t2 WHERE t2.id = t1.id);
ID T1N T1WORD
---------- ---------- ------------
0 0 Zero
1 10 Ten
thx 🙂