Joins

Joins combine results from two or more table expressions (tables, external tables, views) to form a new result set.

The combination of these table expressions ca nbe based on a set of conditions, such as equality of columns.

Joins are useful when data in tables are related. For example, when two tables contain one or more columns in common, so that rows can be matched or correlated with rows from the other table.

Syntax

table_ref ::=
    | left_side join_type right_side
        [ ON value_expr ]
    | table_alias

join_type ::=
    [ INNER ] [ join_hint ] JOIN
    | LEFT [ OUTER ] [ join_hint ] JOIN
    | RIGHT [ OUTER ] [ join_hint ] JOIN
    | CROSS [ join_hint ] JOIN

join_hint ::=
    MERGE | LOOP

Join types

Inner join

left_side [ INNER ] JOIN right_side ON value_expr
left_side [ INNER ] JOIN right_side USING ( join_column [, ... ] )

This is the default join type. Rows from the left_side and right_side that match the condition are returned.

An inner join can also be specified by listing several tables in the FROM clause. Omitting the ON or WHERE will result in a CROSS JOIN, where every row of left_side is matched with every row on right_side.

Left outer join

left_side LEFT [ OUTER ] JOIN right_side ON value_expr
left_side LEFT [ OUTER ] JOIN right_side USING ( join_column [, ... ] )

Similar to the inner join - but for every row of left_side that does not match, NULL values are returned for columns on right_side

Right outer join

left_side RIGHT [ OUTER ] JOIN right_side ON value_expr
left_side RIGHT [ OUTER ] JOIN right_side USING ( join_column [, ... ] )

Similar to the inner join - but for every row of right_side that does not match, NULL values are returned for columns on left_side

Cross join

left_side CROSS JOIN right_side

A cartesian product - all rows match on all sides. For every row from left_side and right_side, the result set will contain a row with all columns from left_side and all columns from right_side.

The CROSS JOIN can’t have an ON clause, but WHERE can be used to limit the result set.

Join conditions

ON value_expr

The ON condition is an expression that evaluates to a boolean to identify whether the rows match.

For example, ON left_side.name = right_side.name matches when both name columns match.

For LEFT and RIGHT joins, the ON clause is optional. However, if it is not specified, the result is a computationally intensive CROSS JOIN.

Tip

SQream DB does not support the USING syntax. However, queries can be easily rewritten. left_side JOIN right_side using (name) is equivalent to ON left_side.name = right_side.name

Examples

Assume a pair of tables with the following structure and content:

CREATE TABLE left_side (x INT);
INSERT INTO left_side VALUES (1), (2), (4), (5);

CREATE TABLE right_side (x INT);
INSERT INTO right_side VALUES (2), (3), (4), (5), (6);

Inner join

In inner joins, values that are not matched do not appear in the result set.

t=> SELECT * FROM left_side AS l JOIN right_side AS r
.>         ON l.x = r.x;
x | x0
--+---
2 |  2
4 |  4
5 |  5

Left join

Note

Note the null values for 1 which were not matched. SQream DB outputs nulls last.

t=> SELECT * FROM left_side AS l LEFT JOIN right_side AS r
.>         ON l.x = r.x;
x | x0
--+---
2 |  2
4 |  4
5 |  5
1 | \N

Right join

Note

Note the null values for 3, 6 which were not matched. SQream DB outputs nulls last.

t=> SELECT * FROM left_side AS l LEFT JOIN right_side AS r
.>         ON l.x = r.x;
x  | x0
---+---
2  |  2
4  |  4
5  |  5
\N |  3
\N |  6

Cross join

t=> SELECT * FROM left_side AS l CROSS JOIN right_side AS r;
x | x0
--+---
1 |  2
1 |  3
1 |  4
1 |  5
1 |  6
2 |  2
2 |  3
2 |  4
2 |  5
2 |  6
4 |  2
4 |  3
4 |  4
4 |  5
4 |  6
5 |  2
5 |  3
5 |  4
5 |  5
5 |  6

Specifying multiple comma-separated tables is equivalent to a cross join, that can then be filtered with a WHERE clause..

t=> SELECT * FROM left_side l, right_side r;
x | x0
--+---
1 |  2
1 |  3
1 |  4
1 |  5
1 |  6
2 |  2
2 |  3
2 |  4
2 |  5
2 |  6
4 |  2
4 |  3
4 |  4
4 |  5
4 |  6
5 |  2
5 |  3
5 |  4
5 |  5
5 |  6

t=> SELECT * FROM left_side l, right_side r WHERE (r.x=l.x);
x | x0
--+---
2 |  2
4 |  4
5 |  5

Join hints

Join hints can be used to override the query compiler and choose a particular join algorithm.

The available algorithms are LOOP (corresponding to non-indexed nested loop join algorithm), and MERGE (corresponding to sort merge join algorithm).

If no algorithm is specified, a loop join is performed by default.

t=> SELECT * FROM left_side AS l INNER MERGE JOIN right_side AS r  ON l.x = r.x;
x | x0
--+---
2 |  2
4 |  4
5 |  5

t=> SELECT * FROM left_side AS l INNER LOOP JOIN right_side AS r  ON l.x = r.x;
x | x0
--+---
2 |  2
4 |  4
5 |  5