Joins

CommentsProcedural SQL (PSQL) Statements

Joins

Joins combine data from two sources into a single set.This is done on a row-by-row basis and usually involves checking a join condition to determine which rows should be merged and appear in the resulting dataset.There are several types (INNER, OUTER) and classes (qualified, natural, etc.) of joins, each with its own syntax and rules.

Since joins can be chained, the datasets involved in a join may themselves be joined sets.

Syntax

SELECT
   ...
   FROM <table-reference> [, <table-reference> ...]
   [...]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression [, <value-expression> ...]

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]

<derived-table> ::= (<query-expression>)

<parenthesized-joined-table> ::=
    (<parenthesized-joined-table)
  | (<joined-table>)

<joined-table> ::=
    <cross-join>
  | <natural-join>
  | <qualified-join>

<cross-join>
  <table-reference> CROSS JOIN <table-primary>

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  { ON <search-condition>
  | USING (<column-name-list>) }

Table 1. Arguments for `JOIN` Clauses
Argument	Description
table-name	Name of a table or view
query-name	Name of a CTE
package-name	Name of a package
procedure-name	Name of a selectable stored procedure
procedure-args	Selectable stored procedure input parameter(s)
derived-table	Derived table query expression
correlation-name	The alias of a data source (table, view, procedure, CTE, derived table)
column-name	Name or alias for a column in a relation, CTE or derived table
search-condition	Join condition (criterion)
column-name-list	List of aliases of the columns of a derived table, or the list of columns used for an equi-join

Inner vs. Outer Joins

A join combines data rows from two sets (usually referred to as the left set and the right set).By default, only rows that meet the join condition (i.e. that match at least one row in the other set when the join condition is applied) make it into the result set.This default type of join is called an inner join.Suppose we have the following two tables:

Table A
ID	S
87	Just some text
235	Silence

Table B
CODE	X
-23	56.7735
87	416.0

If we join these tables like this:

select *
  from A
  join B on A.id = B.code;

then the result set will be:

CODE

Just some text

416.0

The first row of A has been joined with the second row of B because together they met the condition “A.id = B.code”.The other rows from the source tables have no match in the opposite set and are therefore not included in the join.Remember, this is an INNER join.We can make that fact explicit by writing:

select *
  from A
  inner join B on A.id = B.code;

However, since INNER is the default, it is usually omitted.

It is perfectly possible that a row in the left set matches several rows from the right set or vice versa.In that case, all those combinations are included, and we can get results like:

ID	S	CODE	X
87	Just some text	87	416.0
87	Just some text	87	-1.0
-23	Don’t know	-23	56.7735
-23	Still don’t know	-23	56.7735
-23	I give up	-23	56.7735

Sometimes we want (or need) all the rows of one or both of the sources to appear in the joined set, even if they don’t match a record in the other source.This is where outer joins come in.A LEFT outer join includes all the records from the left set, but only matching records from the right set.In a RIGHT outer join it’s the other way around.A FULL outer joins include all the records from both sets.In all outer joins, the “holes” (the places where an included source record doesn’t have a match in the other set) are filled up with NULLs.

To make an outer join, you must specify LEFT, RIGHT or FULL, optionally followed by the keyword OUTER.

Below are the results of the various outer joins when applied to our original tables A and B:

select *
  from A
  left outer join B on A.id = B.code;

ID	S	CODE	X
87	Just some text	87	416.0
235	Silence	<null>	<null>

select *
  from A
  right outer join B on A.id = B.code

ID	S	CODE	X
<null>	<null>	-23	56.7735
87	Just some text	87	416.0

select *
  from A
  full outer join B on A.id = B.code

ID	S	CODE	X
<null>	<null>	-23	56.7735
87	Just some text	87	416.0
235	Silence	<null>	<null>