There are two available join formats, namely the original Oracle proprietary format and the recently introduced ANSI standard join format. Both of the two different formats provide equally good performance.
SELECT { [ [schema.]table. | [alias.] ]
{ column | expression [, ... ] } | * }
FROM [schema.]table [alias] [, …]
[ WHERE
[ [schema.]table.|alias.] { column | expression [(+)] }
comparison condition
[ [schema.]table.|alias.] { column | expression [(+)] }
[ { AND | OR } [ NOT ] … ]
];
The outer join operator is the (+) operator and appears on the side of the expression containing the data source or table which is deficient in information. Here is an example.
SELECT a.name, pl.title, e.ISBN, pu.name FROM author a, publication pl, edition e, publisher pu WHERE pl.author_id = a.author_id AND e.publication_id = pl.publication_id AND pu.publisher_id = e.publisher_id;
SELECT { { [ [schema.]table.|alias.] { column | expression } [, … ] } | * }
FROM [schema.]table [alias]
[
CROSS JOIN [schema.]table [alias]
| NATURAL [ INNER | [ LEFT | RIGHT | FULL ] OUTER ]
JOIN [schema.]table [alias]
| {
[ INNER | [ LEFT | RIGHT | FULL ] OUTER ] JOIN [schema.]table [alias]
{
ON (column = column [ { AND | OR } [ NOT ] column = column ... ] )
| USING (column [, column ... ])
}
}
]
[ WHERE ... ] [ ORDER BY ... ];
This is the same example as the previous one but using the ANSI standard join format.
SELECT a.name, pl.title, e.ISBN, pu.name FROM author a JOIN publication pl USING(author_id) JOIN edition e USING(publication_id) JOIN publisher pu USING (publisher_id);
The USING and ON clauses are used exclusively in the ANSI standard join format in order to qualify column names to be joined on.
The USING clause is used to join two tables where two tables have column names which are the same, where those columns do not have matching values in them. For instance in the ERD for the Books schema shown below the Author and Publisher tables both have NAME columns; the values in those two NAME columns are completely different and thus joining AUTHOR.NAME and PUBLISHER.NAME is completely meaningless. Again using the same example as before, note that the query has a NAME column on both the Author and Publisher tables.
SELECT a.name, pl.title, e.ISBN, pu.name FROM author a JOIN publication pl USING(author_id) JOIN edition e USING(publication_id) JOIN publisher pu USING (publisher_id);
The ON clause is used to join two tables where join column names are different.
SELECT a.name, pl.title, ca.coauthor_id FROM author a JOIN publication pl ON (pl.author_id = a.author_id) JOIN coauthor ca ON (ca.coauthor_id = a.author_id);
These joins rely on comparison conditions between expressions and are effectively a subset of most other join types.
Equi joins join based on equality (=) and are the fastest option since they find exact matches on single rows. The query following effectively contains three Equi joins.
SELECT a.name, pl.title, e.ISBN, pu.name FROM author a, publication pl, edition e, publisher pu WHERE pl.author_id = a.author_id AND e.publication_id = pl.publication_id AND pu.publisher_id = e.publisher_id;
Anti joins join based on inequality (!=, <> or NOT) and are slow because they avoid indexes and use full scans; everything other than the specified value must be checked. The following query is completely pointless but is demonstrative.
SELECT a.name, pl.title FROM author a, publication pl WHERE pl.author_id != a.author_id;
Range joins are sometimes called theta joins and join based on a range specifier (>, <, >=, <= or BETWEEN). Range joins will range scan indexes if possible. If too many rows are read at once a full scan will result. Once again a demonstrative but completely meaningless query.
SELECT a.name, pl.title FROM author a, publication pl WHERE pl.author_id >= a.author_id;
A semi join is Semi because it is both a type of join and a type of subquery. It is a semi join because it does a kinda-sort-of join in that values are not returned from the subquery but verified against, but the act of calling a subquery is effectively a join with another query anyway. IN validates against a list and EXISTS returns a boolean result.
IN is best for checking values in a literal list of values or with subqueries against very small, static data tables. All elements or rows in the subquery are processed. An IN set membership subquery is known as non-correlated subquery in that there is no connection or possible dependence between calling and called subquery.
SELECT name FROM author WHERE author_id IN (1,2,3,4,5); NAME -------------------------------- Jerry Pournelle Larry Niven Isaac Azimov James Blish Orson Scott Card
EXISTS will be faster than IN for anything but literal lists or very small, static data tables. EXISTS has two performance advantages. Firstly, not every row in the subquery is necessarily read since EXISTS only requires a boolean result of TRUE or FALSE. Once a TRUE result (EXISTS) or FALSE result (NOT EXISTS) is found the subquery will cease execution and pass its result back to the calling query. Secondly EXISTS can use correlated subqueries establishing a connection between calling query and called subquery. A value can be passed into the subquery from the calling query for verification between individual rows both in calling query and subquery. This can greatly enhance performance by increasing the intersection factor of the semi join and by allowing use of indexing between calling query and subquery.
SELECT pl.title FROM publication pl WHERE EXISTS (SELECT author_id FROM author WHERE author_id = pl.author_id); TITLE ---------------------------------------------------------- Cities in Flight A Case of Conscience Foundation Second Foundation Foundation and Empire Foundation's Edge Prelude to Foundation The Complete Works of Shakespeare Lucifer's Hammer Footfall Ringworld 11 rows selected.
A cross join merges all data selected from both tables into a single result set by matching every row in one table with every row in the other table. The number of rows returned is the product or multiplication of the number of rows in each table; thus Cartesian Product.
The queries below show 6 authors, 11 publications and a resulting cross join between the two of 66 rows.
SELECT COUNT(*) FROM author;
COUNT(*)
----------
6
SELECT COUNT(*) FROM publication;
COUNT(*)
----------
11
SELECT COUNT(*) FROM author CROSS JOIN publication;
COUNT(*)
----------
66
A natural join combines rows from both tables using matching column names and column values. It provides the intersection of two row sets, the rows common to both sets.
In the following example the natural join will occur naturally on the column name common to both tables, the AUTHOR_ID column. Note how the AUTHOR_ID column is not retrieved with an alias qualifier.
SELECT author_id, a.name, pl.title
FROM author a NATURAL JOIN publication pl;
AUTHOR_ID NAME TITLE
---------- -------------------------------- -----------------------------------
2 James Blish Cities in Flight
2 James Blish A Case of Conscience
3 Isaac Azimov Foundation
3 Isaac Azimov Second Foundation
3 Isaac Azimov Foundation and Empire
3 Isaac Azimov Foundation's Edge
3 Isaac Azimov Prelude to Foundation
6 William Shakespeare The Complete Works of Shakespeare
4 Larry Niven Lucifer's Hammer
4 Larry Niven Footfall
4 Larry Niven Ringworld
11 rows selected.
Outer joins select rows from both tables as with a natural join but including rows from one or both tables that do not have matching rows in the other table. Missing values are replaced with nulls. A union can sometimes be used as a substitute for an outer join.
A left outer join includes all rows from the left table plus all matching rows from the right table. Column values from the right table are replaced with nulls when the matching right side row does not exist in the left side table.
From the previous natural join query there are four authors listed as having publications. There are a total of six authors in the database, two of which do not have publications.
SELECT * FROM author;
AUTHOR_ID NAME
---------- --------------------------------
1 Orson Scott Card
2 James Blish
3 Isaac Azimov
4 Larry Niven
5 Jerry Pournelle
6 William Shakespeare
6 rows selected.
Let's use a left outer join to retrieve all authors and publications, regardless of the existenece of a publication. Note how this first query has no alias qualifiers since all column names are different and the same where required.
SELECT author_id, name, title
FROM author LEFT OUTER JOIN publication USING (author_id);
AUTHOR_ID NAME TITLE
---------- -------------------------------- -------------------------------------
2 James Blish Cities in Flight
2 James Blish A Case of Conscience
3 Isaac Azimov Foundation
3 Isaac Azimov Second Foundation
3 Isaac Azimov Foundation and Empire
3 Isaac Azimov Foundation's Edge
3 Isaac Azimov Prelude to Foundation
6 William Shakespeare The Complete Works of Shakespeare
4 Larry Niven Lucifer's Hammer
4 Larry Niven Footfall
4 Larry Niven Ringworld
5 Jerry Pournelle
1 Orson Scott Card
13 rows selected.
This second query includes the use of alias qualifiers. The ON clause changes to the USING clause since now specific column names in specific tables must be qualified. The resulting query will be the same.
SELECT a.author_id, a.name, pl.title FROM author a LEFT OUTER JOIN publication pl ON (a.author_id = pl.author_id);
Now using the Oracle proprietary format where the outer join operator (+) is placed in the side of the comparison condition which is deficient in information; the Publication table is the table missing rows.
SELECT a.author_id, a.name, pl.title
FROM author a, publication pl
WHERE a.author_id = pl.author_id(+);
AUTHOR_ID NAME TITLE
---------- -------------------------------- ----------------------------------
1 Orson Scott Card
2 James Blish Cities in Flight
2 James Blish A Case of Conscience
3 Isaac Azimov Foundation
3 Isaac Azimov Second Foundation
3 Isaac Azimov Foundation and Empire
3 Isaac Azimov Foundation's Edge
3 Isaac Azimov Prelude to Foundation
4 Larry Niven Lucifer's Hammer
4 Larry Niven Footfall
4 Larry Niven Ringworld
5 Jerry Pournelle
6 William Shakespeare The Complete Works of Shakespeare
13 rows selected.
A right outer join includes all rows from the table on the right plus matching rows from the left table; the opposite of the left outer join.
The simplest method of showing the difference between a left and outer join is to use the opposite of the left outer join queries used in the previous examples, simply reversing the join orders.
SELECT author_id, name, title FROM publication RIGHT OUTER JOIN author USING (author_id); SELECT a.author_id, a.name, pl.title FROM author a, publication pl WHERE pl.author_id(+) = a.author_id;
A full outer join includes all rows from both tables, both matching and non-matching, with nulls replacing missing values. A full outer join not is the same as a Cartesian Product. Full outer joins are quite often as pointless to use as Cartesian Products and their use is likely to indicate data model problems.
SELECT author_id, name, title FROM publication FULL OUTER JOIN author USING (author_id);
A self join, sometimes called a fish hook join, simply joins a table to itself by matching rows in the same table to other rows or the same rows in the same table.
SELECT parent.name, child.name FROM subject parent, subject child WHERE child.parent_id = parent.subject_id; NAME NAME -------------------------------- -------------------------------- Non-Fiction Self Help Non-Fiction Esoteric Non-Fiction Metaphysics Non-Fiction Computers Fiction Science Fiction Fiction Fantasy Fiction Drama Fiction Whodunnit Fiction Suspense Fiction Literature Literature Poetry Literature Victorian Literature Shakespearian Literature Modern American Literature 19th Century American Fiction Subject name 16 rows selected.
A mutable join is a join of more than two tables. A complex join is a mutable join with added filtering. The more tables in a mutable complex join the worse the performance of the query is likely to be. The following query is a mutable join.
SELECT a.name, pl.title, e.ISBN, pu.name FROM author a, publication pl, edition e, publisher pu WHERE pl.author_id = a.author_id AND e.publication_id = pl.publication_id AND pu.publisher_id = e.publisher_id;
This is the ANSI format of the same mutable join.
SELECT a.name, pl.title, e.ISBN, pu.name FROM author a JOIN publication pl USING(author_id) JOIN edition e USING(publication_id) JOIN publisher pu USING (publisher_id);
The next query is a complex join because it has more than two tables plus some filtering. Note how filtering is applied before any joins are executed; this will help to increase performance by reducing the number of rows joined.
SELECT a.name, pl.title, e.ISBN, pu.name FROM author a, publication pl, edition e, publisher pu WHERE a.name LIKE '%a%' AND pl.author_id = a.author_id AND e.publication_id = pl.publication_id AND pu.publisher_id = e.publisher_id;
This is the same complex join in ANSI format. Note how filtering with the WHERE clause is applied after all joins are completed; this is not how the Optimizer will execute this query - filtering will be applied first to reduce the number of rows joined.
SELECT a.name, pl.title, e.ISBN, pu.name FROM author a JOIN publication pl USING(author_id) JOIN edition e USING(publication_id) JOIN publisher pu USING (publisher_id) WHERE a.name LIKE '%a%';