Join Operators

JOIN operators compare tables/views, two at a time, by

  1. Specifying column(s) from each

  2. Comparing the values in the columns row by row.

  3. Concatenating rows where the comparison is TRUE.

You can only make comparisons between values of similar type.

You can also join tables using a WHERE clause; using a JOIN operator provides an alternate syntax, but note that the methods are different, as the JOIN clause always takes effect before any WHERE clause is applied.

Usage

SELECT select-statement
FROM {table | view | joined-table}
{INNER | CROSS | NATURAL | [NATURAL] {LEFT | RIGHT | FULL [OUTER]} JOIN} {table | view | joined-table}
{ON search-condition} | {USING(colname, colname...)}
[WHERE where-condition]

Notes

The following join types are supported by Kognitio:

  • INNER - gives the same result as a join using a WHERE clause. Only rows fully satisfying the join expressions are returned

  • CROSS - is effectively the same as a Cartesian JOIN: every row in the left hand table is joined to every row in the right hand table

  • NATURAL - offers a further specialization of an equi-join. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named column.

  • LEFT OUTER - preserves all rows from the left hand table. Can be viewed as the union of the equivalent INNER JOIN and the remaining rows in the left table but not the right table.

  • RIGHT OUTER - preserves all rows in the right hand table. Can be viewed as the union of the equivalent INNER JOIN and the remaing rows in the right table but not the left table.

  • FULL OUTER - preserves rows on both sides of the join. Can be viewed as the union of the equivalent INNER JOIN and the set of rows in the left table but not in the right table, and the set of rows in the right table but in not the left table.

If you want to use an OUTER JOIN, you must specify it explicitly. If you don’t, the system performs an INNER JOIN.

ON search-condition is commonly used syntax, and involves a comparison between the matching column(s) in the two joined tables. For example:

ON customer.c_custkey = ordertab.o_custkey

If you do a SELECT * from two tables joined in this way, all columns, including both customer.c_custkey and ordertab.o_custkey are returned. Note that the names of the two columns do not need to be identical.

The alternative USING (colname, colname,...) syntax uses common column name(s) from the joined tables in the brackets. For example:

USING(ps_partkey, ps_suppkey)

A single instance of the ps_partkey and ps_suppkey is returned. Note that the names of the common columns must be identical. So attempting to use the construction with customer.c_custkey and ordertab.o_custkey fails, because the column names are different.

The ON or USING clause is evaluated before the optional WHERE clause, i.e. if we start with a FULL OUTER JOIN between the two tables all of the rows that pass the ON clause get through. Any rows from the left/right hand side that do not join to a row from the other side from the ON clause get joined to NULL and then get through. Finally the WHERE clause is applied to result rows.

If names clash in the tables you are joining, give the table name as well as the column name, and separate them with a dot, for example, supplier.s_name, part.p_name. (If necessary, include the schema name as well, for example, myschema.supplier.s_name, myschema.part.p_name.)

It is only valid to use parentheses to enclose join expressions in a FROM clause; therefore a statement of the form FROM (a LEFT JOIN b ON a.x = b.y), c would be valid whereas FROM (a, b), c would not.

Alternative OUTER JOIN syntax

It is also possible to specify outer joins using the (+) syntax with the WHERE clause. If a column in a WHERE clause is followed by a (+), this indicates that NULL rows should be added in those cases where no match exists. For example:

WHERE customer.c_custkey = ordertab.o_custkey(+)

Indicates that rows from the customer table should be included in the result, even if there are not any corresponding rows in the ordertab table.

Only a left or a right outer joins can be specified with the (+) syntax. A (+) cannot be used on both sides of the = to specify a full outer join.

Understanding joins with worked examples

In these examples we use the Kognitio training dataset. The examples use the customer and ordetab tables, where normally there are some customers who haven’t placed any orders, but all orders match with a customer. To demonstrate the features of INNER and OUTER JOINS, we add the following orphaned order, which doesn’t have a corresponding customer:

INSERT INTO ordertab VALUES (66699, 13, '0', 999, DATE '1998-11-24',
                             TIME '17:30:00', 'high', TIMESTAMP '1998-12-24 12:00:00' )

Note: If the ordertab table was created using a references-spec, so giving it referential integrity, you could not insert this row.

Example 1 - A simple INNER JOIN

A join between customer and ordertab using WHERE:

SELECT customer.c_custkey AS custno,
       customer.c_name AS custname,
       customer.c_acctbal AS balance,
       ordertab.o_orderkey AS orderno,
       ordertab.o_totalprice AS totalprice
FROM customer, ordertab
WHERE customer.c_custkey = ordertab.o_custkey
ORDER BY 1, 4

produces the following result. There are no records for customers 3, 6, 8, 9 who haven’t placed an order, and no record for the new order 66699. (Customers who have placed more than one order have multiple entries.)

custno

custname

balance

orderno

totalprice

1

Andys autos

0.00

66601

100.00

1

Andys autos

0.00

66602

1000.00

2

Gordons garage

1230.50

66607

100.00

2

Gordons garage

1230.50

66608

100.00

4

Bert Brown

0.00

66603

500.00

5

Bert Browne

0.00

66604

1100.00

5

Bert Browne

0.00

66605

250.00

5

Bert Browne

0.00

66606

100.00

7

Burt Browne

0.00

66609

100.00

You get the same result using the INNER JOIN syntax:

SELECT customer.c_custkey custno,
       customer.c_name customer,
       customer.c_acctbal balance,
       ordertab.o_orderkey orderno,
       ordertab.o_totalprice totalprice
FROM customer INNER JOIN ordertab
ON customer_a.c_custkey = ordertab.o_custkey
ORDER BY 1

OUTER JOINS include the “missing” records, but which records are included depends on the type of outer join.

Example 2 - A LEFT OUTER Join

LEFT OUTER joins specify that all rows from the ‘left’ table (customer in our example) are returned. All rows from the left table that don’t meet the condition specified are included in the results set, and output columns from the other table are set to NULL.

This example joins the two tables on custkey, and preserves the unmatched rows from the left (customer) table. The customer table is matched with the ordertab table on custkey:

SELECT customer.c_custkey custno,
       customer.c_name customer,
       customer.c_acctbal acctbal,
       ordertab.o_orderkey orderno,
       ordertab.o_totalprice totalprice
FROM customer
LEFT OUTER JOIN ordertab
ON customer.c_custkey = ordertab.o_custkey
ORDER BY 1, 4

custno

customer

acctbal

orderno

totalprice

1

Andys autos

0.00

66601

100.00

1

Andys autos

0.00

66602

1000.00

2

Gordons garage

1230.50

66607

100.00

2

Gordons garage

1230.50

66608

100.00

3

MikeTheMechanic

-983.00

<NULL>

<NULL>

4

Bert Brown

0.00

66603

500.00

5

Bert Browne

0.00

66604

1100.00

5

Bert Browne

0.00

66605

250.00

5

Bert Browne

0.00

66606

100.00

6

Burt Brown

0.00

<NULL>

<NULL>

7

Burt Browne

0.00

66609

100.00

8

John Brown

0.00

<NULL>

<NULL>

9

David Brown

0.00

<NULL>

<NULL>

Customers who haven’t placed any orders are included, and there is a <NULL> in the columns that derive from the ordertab table, but note that there is no record for order 66699.

Note that you can obtain the same results using the following UNION with the WHERE clause, which is equivalent to a LEFT OUTER JOIN:

SELECT c_custkey, c_name, c_acctbal, o_orderkey, o_totalprice
FROM customer, ordertab
WHERE customer.c_custkey = ordertab.o_custkey
UNION
SELECT c_custkey, c_name, c_acctbal,
       CAST(NULL AS INT), CAST(NULL AS DEC(12,2))
FROM customer
WHERE c_custkey NOT IN (SELECT o_custkey FROM ordertab)
ORDER BY 1

Here, the “missing” columns from the ordertab table are replaced by literals cast as NULLs.

Example 3 - The USING Syntax

The following queries illustrate a LEFT OUTER JOIN using the ON and the less common USING syntax. Here, the tables part_a, supplier_a and suppkey_a have common keys (partkey and suppkey) with identical names, and both part_a and supplier_a have a ‘name’ column. Both queries give the same result:

SELECT p.name AS partname,
       COALESCE(s.name, 'None') AS supplier,
       ps.availqty AS quantity
FROM part_a p
LEFT OUTER JOIN partsupp_a ps
ON p.partkey = ps.partkey
LEFT OUTER JOIN supplier_a s
ON s.suppkey = ps.suppkey
ORDER BY 1, 2, 3;

SELECT p.name AS partname,
       COALESCE(s.name, 'None') AS supplier,
       availqty AS quantity
FROM part_a p
LEFT OUTER JOIN partsupp_a ps
USING(partkey)
LEFT OUTER JOIN supplier_a s
USING(suppkey)
ORDER BY 1, 2, 3;

partname

supplier

quantity

Air cleaner

Daves deliveries

10

Air cleaner

Petes parts

7

Air filter element condition indicator

Daves deliveries

100

Air filter element condition indicator

Petes parts

10

Air filter element condition indicator

Sams supplies

10

Brake vacuum pump

Daves deliveries

10

Clutch fluid reservoir

Petes parts

5

Engine oil filler cap

Daves deliveries

100

Fuel filter

Sams supplies

1000

Fuel injection pump

None

<NULL>

Fuel lift pump

Sams supplies

10

Heater

Daves deliveries

5

Heater

Petes parts

1

Turbocharger

Daves deliveries

1

Turbocharger

Sams supplies

1

VIN plate

Petes parts

10

Note that you can only make use of this syntax if your tables are set up to have common key columns. Where data is frequently imported from a variety of different sources, this may seldom be the case.

Example 4 - A RIGHT OUTER Join

RIGHT OUTER JOINS specify that all rows from the right table (ORDERTAB in our example) are included in the results set, even if they don’t meet the condition specified. The output columns that correspond to the other table are set to NULL.

This example joins two tables on custkey, and preserves the unmatched rows from the right table ordertab. The customer table is matched with the ordertab table on custkey:

SELECT customer.c_custkey custno,
       customer.c_name custname,
       customer.c_acctbal acctbal,
       ordertab.o_orderkey orderno,
       ordertab.o_totalprice totalprice
FROM customer
RIGHT OUTER JOIN ordertab
ON customer.c_custkey = ordertab.o_custkey
ORDER BY 1, 4

This produces the following result.

custno

custname

acctbal

orderno

totalprice

1

Andys autos

0.00

66601

100.00

1

Andys autos

0.00

66602

1000.00

2

Gordons garage

1230.50

66607

100.00

2

Gordons garage

1230.50

66608

100.00

4

Bert Brown

0.00

66603

500.00

5

Bert Browne

0.00

66604

1100.00

5

Bert Browne

0.00

66605

250.00

5

Bert Browne

0.00

66606

100.00

7

Burt Browne

0.00

66609

100.00

<NULL>

<NULL>

<NULL>

66699

999.00

Here, the unmatched order record for 66699 is included, but there are no records for customers who haven’t placed orders.

Note that you can obtain the same results using the following UNION and WHERE, which is equivalent to the RIGHT OUTER JOIN:

SELECT c_custkey, c_name, c_acctbal, o_orderkey, o_totalprice
FROM customer, ordertab
WHERE customer.c_custkey = ordertab.o_custkey
UNION
SELECT CAST(NULL AS INT), CAST(NULL AS VARCHAR(25)),
       CAST(NULL AS DEC(12,2)), o_orderkey, o_totalprice
FROM ordertab
WHERE o_custkey NOT IN (SELECT c_custkey FROM customer)
ORDER BY 1

Here, the ‘missing’ columns from the customer table are replaced by literals cast as NULLs.

Example 5 - A FULL OUTER JOIN

If a row from either table doesn’t match the selection criteria, FULL OUTER JOIN specifies that the row is included in the results set, and any output columns that correspond to the other table are set to NULL.

A FULL OUTER JOIN between the CUSTOMER and ORDERTAB tables:

SELECT customer.c_custkey custno,
       customer.c_name custname,
       customer.c_acctbal acctbal,
       ordertab.o_orderkey orderno,
       ordertab.o_totalprice totalprice
FROM customer FULL OUTER JOIN ordertab
ON customer.c_custkey = ordertab.o_custkey
ORDER BY 1, 4

custno

custname

acctbal

orderno

totalprice

1

Andys autos

0.00

66601

100.00

1

Andys autos

0.00

66602

1000.00

2

Gordons garage

1230.50

66607

100.00

2

Gordons garage

1230.50

66608

100.00

3

MikeTheMechanic

-983.00

<NULL>

<NULL>

4

Bert Brown

0.00

66603

500.00

5

Bert Browne

0.00

66604

1100.00

5

Bert Browne

0.00

66605

250.00

5

Bert Browne

0.00

66606

100.00

6

Burt Brown

0.00

<NULL>

<NULL>

7

Burt Browne

0.00

66609

100.00

8

John Brown

0.00

<NULL>

<NULL>

9

David Brown

0.00

<NULL>

<NULL>

<NULL>

<NULL>

<NULL>

66699

999.00

Now, there are records for customers who haven’t placed an order, and for the unmatched ORDERS record 66699.

Example 6 - Joins Involving Multiple Tables

The previous examples are based on joins between two tables. You can also join multiple tables, using any required combination of inner and outer joins. The following example uses the customer and ordertab tables, together with the nation table.

Construct an inner join between customer and ordertab, and a full outer join to nation. This identifies the nationality of customers who have placed orders, and identifies any country where no customers have placed orders.:

SELECT customer_a.c_custkey custno,
       customer_a.c_name custname,
       nation.n_name,
       customer_a.c_acctbal acctbal,
       ordertab.o_orderkey orderno,
       ordertab.o_totalprice totalprice
FROM customer_a INNER JOIN ordertab
ON customer_a.c_custkey = ordertab.o_custkey
FULL OUTER JOIN nation
ON customer_a.c_nationkey = nation.n_nationkey
ORDER BY 1, 3, 5

custno

custname

nation.n_name

Acctbal

orderno

totalprice

<NULL>

<NULL>

Canada

<NULL>

<NULL>

<NULL>

<NULL>

<NULL>

France

<NULL>

<NULL>

<NULL>

1

Andys autos

UK

0.00

66601

100.00

1

Andys autos

UK

0.00

66602

1000.00

2

Gordons garage

UK

1230.50

66608

100.00

2

Gordons garage

UK

1230.50

66607

100.00

4

Bert Brown

UK

0.00

66603

500.00

5

Bert Browne

UK

0.00

66604

1100.00

5

Bert Browne

UK

0.00

66605

250.00

5

Bert Browne

UK

0.00

66606

100.00

7

Burt Browne

UK

0.00

66609

100.00

<NULL>

<NULL>

USA

<NULL>

<NULL>

<NULL>

Example 7 - Joins with Aggregation

Use an INNER JOIN between the customer and ordertab tables, and count the records:

SELECT name,
       COUNT(o_orderkey)
FROM customer
INNER JOIN ordertab
ON customer.c_custkey = order.o_custkey
GROUP BY 1
ORDER BY 1

This produces the following result.

c_name

COUNT ( o_orderkey )

Andys autos

2

Bert Brown

1

Bert Browne

3

Burt Browne

1

Gordons garage

2

There are no records for customers who haven’t placed any orders, and no record for order 66699.

Using a LEFT OUTER join between the customer and ordertab tables, and count the records:

SELECT name,
       COUNT(o_orderkey)
FROM customer
LEFT OUTER JOIN ordertab
ON customer.c_custkey = order.o_custkey
GROUP BY 1
ORDER BY 1

This includes records for customers who haven’t placed an order, but not for order 66699 where there is no customer record.

c_name

COUNT ( o_orderkey )

Andys autos

2

Bert Brown

1

Bert Browne

3

Burt Brown

0

Burt Browne

1

David Brown

0

Gordons garage

2

John Brown

0

MikeTheMechanic

0

If you run the query again using a RIGHT OUTER JOIN, you obtain customers who have placed orders, together with the unmatched order.

An aggregating FULL OUTER JOIN` between the ``customer and ordertab tables counts the records for all customers and all orders placed:

SELECT c_name, COUNT(o_orderkey)
FROM customer FULL OUTER JOIN ordertab
ON customer.c_custkey = ordertab.o_custkey
GROUP BY 1
ORDER BY 1

There is a <NULL> under name for order 66699.

c_name

COUNT ( o_orderkey )

Andys autos

2

Bert Brown

1

Bert Browne

3

Burt Brown

0

Burt Browne

1

David Brown

0

Gordons garage

2

John Brown

0

MikeTheMechanic

0

<NULL>

1