Contents

Joining data

For performance and size reasons frequently data is split into multiple tables. JOIN operations merge two tables based on a matching column or columns and allow returning columns from both tables.

In the examples below we will use the following tables:

table_1 is

Table 1

A

B

1

2

2

3

3

4

and table_2 is

Table 2

A

B

C

1

2

3

2

3

4

3

4

5

CROSS

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

The CROSS JOIN returns a cartesian product of the selected columns thus it should be used with caution as the number of results is a product of the unique numbers in each of the columns.

Example

SELECT A.<col_1>
    , B.<col_2>
FROM <table_1> AS A
CROSS JOIN <table_2> AS B

Using the tables defined above, The following code

SELECT A.A AS col_1
    , B.A AS col_2
FROM table_1 AS A
CROSS JOIN table_2 AS B

will return

Table 2

col_1

col_2

1

1

1

2

1

3

2

1

2

2

2

3

3

1

3

2

3

3

INNER

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

The INNER JOIN returns rows from both tables for all overlapping values found in the matching column or columns.

Example

SELECT A.<col_1>
    , B.<col_2>
FROM <table_1> AS A
INNER JOIN <table_2> AS B
    ON A.<col_3> = B.<col_3>

Using the tables defined above, The following code

SELECT A.A AS col_1
    , B.A AS col_2
    , B.B AS col_3
FROM table_1 AS A
INNER JOIN table_2 AS B
    ON A.A = B.B

will return

Table 2

col_1

col_2

col_3

2

1

2

3

2

3

FULL OUTER

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

The FULL OUTER JOIN returns rows from both tables for all distinct values found in the matching column or columns. NULL values are returned for not matched rows from either of the tables.

See also

LEFT OUTER

Example

SELECT A.<col_1>
    , B.<col_2>
FROM <table_1> AS A
FULL OUTER JOIN <table_2> AS B
    ON A.<col_3> = B.<col_3>

Using the tables defined above, The following code

SELECT A.A AS col_1
    , B.A AS col_2
    , B.B AS col_3
FROM table_1 AS A
FULL OUTER JOIN table_2 AS B
    ON A.A = B.B

will return

Table 2

col_1

col_2

col_3

1

NULL

NULL

2

1

2

3

2

3

NULL

3

4

LEFT OUTER

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

The LEFT OUTER JOIN returns rows from both tables for values from the left table and all matching values in the right table that are matching based on the matching criteria. NULL values are returned for not matched rows from the right table.

See also

FULL OUTER

Example

SELECT A.<col_1>
    , B.<col_2>
FROM <table_1> AS A
LEFT OUTER JOIN <table_2> AS B
    ON A.<col_3> = B.<col_3>

Using the tables defined above, The following code

SELECT A.A AS col_1
    , B.A AS col_2
    , B.B AS col_3
FROM table_1 AS A
LEFT OUTER JOIN table_2 AS B
    ON A.A = B.B

will return

Table 2

col_1

col_2

col_3

1

NULL

NULL

2

1

2

3

2

3
Date and timestamp functions Analytic functions