Table Join Queries

In SynxDB Elastic, the JOIN clause is used to combine rows from two or more tables, based on a related column between them. The JOIN clause is part of the FROM clause in a SELECT statement.

The syntax for the JOIN clause is as follows:

table1_name join_type table2_name [join_condition]

Where:

  • table1_name, table2_name: The names of the tables to be joined.

  • join_type: The type of join, which can be one of the following:

    • [INNER] JOIN

    • LEFT [OUTER] JOIN

    • RIGHT [OUTER] JOIN

    • FULL [OUTER] JOIN

    • CROSS JOIN

    • NATURAL JOIN

  • join_condition: An optional join condition that specifies how to match rows between the two tables. It can be one of the following:

    • ON <join_condition>

    • USING ( <join_column> [, ...] )

    • LATERAL

Note

The ORCA optimizer automatically chooses between Merge Join or Hash Join for FULL JOIN queries based on cost estimation. You do not need to manually specify the JOIN type.

Join types

SynxDB Elastic supports the following join types:

INNER JOIN

INNER JOIN returns the intersection of rows from both tables that satisfy the join condition. In other words, it returns only the matching rows from both tables. If you omit the INNER keyword before JOIN, it defaults to an INNER JOIN.

SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

LEFT OUTER JOIN

LEFT OUTER JOIN (or simply LEFT JOIN) returns all rows from the left table, along with the matched rows from the right table. If there is no match for a row from the left table in the right table, the columns of the right table will be filled with NULL values.

SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

RIGHT OUTER JOIN

RIGHT OUTER JOIN (or simply RIGHT JOIN) is the opposite of LEFT OUTER JOIN. It returns all rows from the right table, along with the matched rows from the left table. If there is no match for a row from the right table in the left table, the columns of the left table will be filled with NULL values.

SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Note

Starting from v2.0.0, Hash Right Join queries can also trigger Dynamic Partition Elimination (DPE) when partition pruning conditions are met, which reduces partition scanning and improves performance.

FULL OUTER JOIN

FULL OUTER JOIN (or simply FULL JOIN) returns all rows from both the left and right tables. For rows from the right table that have no matching rows in the left table, the columns of the left table will be filled with NULL values. For rows from the left table that have no matching rows in the right table, the columns of the right table will be filled with NULL values.

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

CROSS JOIN

CROSS JOIN returns the Cartesian product of the two tables. It combines each row from the left table with each row from the right table. Without a WHERE clause to filter the results, this produces a table with a number of rows equal to the number of rows in the left table multiplied by the number of rows in the right table.

SELECT *
FROM table1
CROSS JOIN table2;

NATURAL JOIN

The NATURAL clause is a shorthand for the USING clause. It can be used when all columns with the same name in both tables are used for the join. If the two tables have no column names in common, NATURAL JOIN is equivalent to CROSS JOIN. You should use NATURAL JOIN with caution, as its reliance on column names can lead to unexpected results.

SELECT *
FROM table1
NATURAL JOIN table2;

Join conditions

Join conditions specify how to match rows between two tables. You can use the following join conditions:

ON clause

The ON clause specifies a boolean expression that determines which rows from the two tables are considered a match. It is similar to a WHERE clause but applies only to the JOIN operation.

SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

USING clause

The USING clause is a shorthand for the ON clause, used when the two tables have one or more columns with the same name. It specifies the common columns to be used for the join. The result of a USING clause is that the output contains only one of each pair of equivalent columns, instead of both.

SELECT *
FROM table1
JOIN table2
USING (column_name);

LATERAL

The LATERAL keyword can be placed before a subquery FROM item. This allows the subquery to reference columns of FROM items that appear before it in the FROM list. (Without LATERAL, SynxDB Elastic would evaluate each subquery independently, so cross-referencing other FROM items would not be possible.)

Examples

Suppose we have two tables: customers and orders.

customers table:

customer_id | customer_name
------------+---------------
1           | John Doe
2           | Jane Smith
3           | Bob Johnson

orders table:

order_id | customer_id | order_date
---------+-------------+------------
1        | 1           | 2023-01-15
2        | 2           | 2023-02-20
3        | 1           | 2023-03-10
4        | 4           | 2024-05-01

Here are some examples using different JOIN types:

INNER JOIN example

This query returns all customers and their orders, including only rows with matching customer IDs.

SELECT customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Result:

customer_name | order_id
--------------+----------
John Doe      | 1
Jane Smith    | 2
John Doe      | 3

LEFT OUTER JOIN example

This query returns all customers and their orders. Customer information is returned even if a customer has no orders.

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

Result:

customer_name | order_id
--------------+----------
John Doe      | 1
Jane Smith    | 2
Bob Johnson   | NULL
John Doe      | 3

RIGHT OUTER JOIN example

This query returns all orders and the customers who placed them. Order information is returned even if an order has no associated customer. In this example, the order with order_id 4 has no associated customer.

SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

Result:

customer_name | order_id
--------------+----------
John Doe      | 1
Jane Smith    | 2
John Doe      | 3
NULL          | 4

FULL OUTER JOIN example

This query returns all customers and orders. If a customer has no orders or an order has no customer, the information for that customer or order is still included in the result.

SELECT customers.customer_name, orders.order_id
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

Result:

customer_name | order_id
--------------+----------
John Doe      | 1
Jane Smith    | 2
Bob Johnson   | NULL
John Doe      | 3
NULL          | 4

CROSS JOIN example

This query returns the Cartesian product of the customers and orders tables.

SELECT customers.customer_name, orders.order_id
FROM customers
CROSS JOIN orders;

Result (partially shown, there are 3 * 4 = 12 rows in total):

customer_name | order_id
--------------+----------
John Doe      | 1
John Doe      | 2
John Doe      | 3
John Doe      | 4
Jane Smith    | 1
Jane Smith    | 2
...