跳转到主要内容

JOIN

Supported Join Types

The join combines columns from two or more tables into a single result set. Databend supports the following join types:

Example Tables

Unless explicitly specified, the join examples on this page are created based on the following tables:

Table "vip_info": This table stores the VIP client information.

Client_IDRegion
101Toronto
102Quebec
103Vancouver

Table "purchase_records": This table lists the purchase records for all the clients.

Client_IDItemQTY
100Croissant2,000
102Donut3,000
103Coffee6,000
106Soda4,000

Table "gift": This table lists the gift options for the VIP clients.

Gift
Croissant
Donut
Coffee
Soda

Inner Join

The inner join returns the rows that meet the join conditions in the result set.

Syntax

SELECT select_list
FROM table_a
[INNER] JOIN table_b
ON join_condition_1
[[INNER] JOIN table_c
ON join_condition_2]...
tip

The keyword INNER is optional.

When you join two tables on a common column with the equal operator, you can use the keyword USING to simplify the syntax.

SELECT select_list
FROM table_a
JOIN table_b
USING join_column_1
[JOIN table_c
USING join_column_2]...

Examples

The following example returns the purchase records of the VIP clients:

SELECT purchase_records.client_id,
purchase_records.item,
purchase_records.qty
FROM vip_info
INNER JOIN purchase_records
ON vip_info.client_id = purchase_records.client_id;

For the definitions of the tables in the example, see Example Tables.

Output:

|102|Donut|3000
|103|Coffee|6000

Natural Join

The natural join joins two tables based on all columns in the two tables that have the same name.

Syntax

SELECT select_list
FROM table_a
NATURAL JOIN table_b
[NATURAL JOIN table_c]...

Examples

The following example returns the purchase records of the VIP clients:

SELECT purchase_records.client_id,
purchase_records.item,
purchase_records.qty
FROM vip_info
NATURAL JOIN purchase_records;

For the definitions of the tables in the example, see Example Tables.

Output:

|102|Donut|3,000
|103|Coffee|6,000

Cross Join

The cross join returns a result set that includes each row from the first table joined with each row from the second table.

Syntax

SELECT select_list
FROM table_a
CROSS JOIN table_b

Examples

The following example returns a result set that assigns each gift option to each VIP client:

SELECT *
FROM vip_info
CROSS JOIN gift;

For the definitions of the tables in the example, see Example Tables.

Output:

101|Toronto|Croissant
101|Toronto|Donut
101|Toronto|Coffee
101|Toronto|Soda
102|Quebec|Croissant
102|Quebec|Donut
102|Quebec|Coffee
102|Quebec|Soda
103|Vancouver|Croissant
103|Vancouver|Donut
103|Vancouver|Coffee
103|Vancouver|Soda

Left Join

The left join returns all records from the left table, and the matching records from the right table. The result is NULL records from the right side, if there is no match.

Syntax

SELECT select_list
FROM table_a
LEFT [OUTER] JOIN table_b
ON join_condition
tip

The keyword OUTER is optional.

Examples

The following example returns the purchase records of all VIP clients, the purchase records will be NULL if the VIP client has no purchases:

SELECT vip_info.client_id,
purchase_records.item,
purchase_records.qty
FROM vip_info
LEFT JOIN purchase_records
ON vip_info.client_id = purchase_records.client_id;

For the definitions of the tables in the example, see Example Tables.

Output:

|101|NULL|NULL
|102|Donut|3000
|103|Coffee|6000

Right Join

The right join returns all records from the right table, and the matching records from the left table. The result is NULL records from the left side, if there is no match.

Syntax

SELECT select_list
FROM table_a
RIGHT [OUTER] JOIN table_b
ON join_condition
tip

The keyword OUTER is optional.

Examples

Imagine we have the following tables:

The following example returns all vip_info of all purchase_records, the vip_info will be NULL if purchase_record does not have the corresponding vip_info.

SELECT vip_info.client_id,
vip_info.region
FROM vip_info
RIGHT JOIN purchase_records
ON vip_info.client_id = purchase_records.client_id;

For the definitions of the tables in the example, see Example Tables.

Output:

NULL|NULL
102|Quebec
103|Vancouver
NULL|NULL

Full Outer Join

The full outer join returns all rows from both tables, matching up the rows wherever a match can be made and placing NULLs in the places where no matching row exists.

Syntax

SELECT select_list
FROM table_a
FULL OUTER JOIN table_b
ON join_condition
tip

The keyword OUTER is optional.

Examples

The following example returns all matched and unmatched rows from both tables:

SELECT vip_info.region,
purchase_records.item
FROM vip_info
FULL OUTER JOIN purchase_records
ON vip_info.client_id = purchase_records.client_id;

For the definitions of the tables in the example, see Example Tables.

Output:

Toronto|NULL
Quebec|Donut
Vancouver|Coffee
NULL|Croissant
NULL|Soda

Left / Right Semi Join

The left semi join returns rows from the left table that have a matching row in the right table. The right semi join returns rows from the right table that have a matching row in the left table.

Syntax

-- Left Semi Join

SELECT select_list
FROM table_a
LEFT SEMI JOIN table_b
ON join_condition

-- Right Semi Join

SELECT select_list
FROM table_a
RIGHT SEMI JOIN table_b
ON join_condition

Examples

The following example returns the VIP clients (Client_ID & Region) who have a purchase record:

SELECT *
FROM vip_info
LEFT SEMI JOIN purchase_records
ON vip_info.client_id = purchase_records.client_id;

For the definitions of the tables in the example, see Example Tables.

Output:

102|Quebec
103|Vancouver

The following example returns the purchase records (Client_ID, Item, and QTY) of the VIP clients:

SELECT *
FROM vip_info
RIGHT SEMI JOIN purchase_records
ON vip_info.client_id = purchase_records.client_id;

For the definitions of the tables in the example, see Example Tables.

Output:

|102|Donut|3000
|103|Coffee|6000

Left / Right Anti Join

The left anti join returns rows from the left table that have NO matching row in the right table. The right anti join returns rows from the right table that have NO matching row in the left table.

Syntax

-- Left Anti Join

SELECT select_list
FROM table_a
LEFT ANTI JOIN table_b
ON join_condition

-- Right Anti Join

SELECT select_list
FROM table_a
RIGHT ANTI JOIN table_b
ON join_condition

Examples

The following example returns the VIP clients (Client_ID & Region) who have NO purchase records:

SELECT *
FROM vip_info
LEFT ANTI JOIN purchase_records
ON vip_info.client_id = purchase_records.client_id;

For the definitions of the tables in the example, see Example Tables.

Output:

101|Toronto

The following example returns the purchase records (Client_ID, Item, and QTY) of non-VIP clients:

SELECT *
FROM vip_info
RIGHT ANTI JOIN purchase_records
ON vip_info.client_id = purchase_records.client_id;

For the definitions of the tables in the example, see Example Tables.

Output:

|100|Croissant|2000
|106|Soda|4000