On this page

Show all

Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ Common HetuEngine SQL Syntax/ HetuEngine DQL SQL Syntax/ JOINS

JOINS

Updated on 2024-12-13 GMT+08:00

View PDF

Data of multiple relations can be combined.

HetuEngine supports the following types of JOIN: CROSS JOIN, INNER JOIN, OUTER JOIN (LEFT JOIN, RIGHT JOIN, FULL JOIN), SEMIN JOIN, and ANTI JOIN.

CROSS JOIN

CROSS JOIN returns the Cartesian product of two relationships. You can specify multiple relations using the CROSS JOIN syntax or the FROM subclause.

The following queries are equivalent:

SELECT * FROM nation CROSS JOIN region;
SELECT * FROM nation, region;

INNER JOIN

Rows can be returned only when two tables contain at least one piece of matched data, which is equivalent to JOIN. The clause can also be converted to an equivalent WHERE statement as follows:

SELECT * FROM nation (INNER) JOIN region ON nation.name=region.name;
SELECT * FROM nation ,region WHERE nation.name=region.name;

OUTER JOIN

OUTER JOIN returns all rows, both matched and unmatched, in both tables. It subdivides further into:

Left outer join: LEFT JOIN or LEFT OUTER JOIN. This clause returns all rows from the left table (nation) and matched rows from the right table (region) based on the left table. If a row in the left table is not matched in the right table, the value of the row in the right table is NULL.
Right outer join: RIGHT JOIN or RIGHT OUTER JOIN. This clause returns all rows from the right table (region) and matched rows from the left table (nation) based on the right table. If a row in the right table is not matched in the left table, the value of the row in the left table is NULL.
Full outer join: FULL JOIN or FULL OUTER JOIN. This clause returns matched rows as long as there are matches in either of the tables. It is equivalent to the combination of LEFT JOIN and RIGHT JOIN.

SELECT * FROM nation LEFT (OUTER) JOIN region ON nation.name=region.name;
SELECT * FROM nation RIGHT (OUTER) JOIN region ON nation.name=region.name;
SELECT * FROM nation FULL (OUTER) JOIN region ON nation.name=region.name;

LATERAL

The LATERAL keyword can be added to the FROM subquery to allow referencing of the columns provided by the FROM item.

SELECT name, x, y FROM nation CROSS JOIN LATERAL (SELECT name || ' :-' AS x) CROSS JOIN LATERAL (SELECT x || ')' AS y);

SEMI JOIN and ANTI JOIN

When a table finds a matched record in another table, semi-join returns the record in the first table. Contrary to conditional join, the table on the left node returns only one record even if several matching records are found on the right node. In addition, no record in the table on the right node is returned. The semi-join usually uses IN or EXISTS as the connection condition.

anti-join is opposite to semi-join. That is, the records in the first table are returned only when no matching record is found in the second table. It is used when not exists or not in is used.

Other supported conditions are as follows:

Multiple conditions in the WHERE clause
Alias relationships
Subscript expressions
Dereference expressions
Forcible conversion expressions
Specific functions

NOTICE:

Currently, multiple semi or anti join expressions are supported only when the columns in the first table are queried in the subsequent join expressions and are not related to other join expressions.

Example:

CREATE SCHEMA testing ;

USE testing;

CREATE TABLE table1(id int, name varchar,rank int);

INSERT INTO table1 VALUES(10,'sachin',1),(45,'rohit',2),(46,'rohit',3),(18,'virat',4),(25,'dhawan',5);

CREATE TABLE table2(serial int,name varchar);

INSERT INTO table2 VALUES(1,'sachin'),(2,'sachin'),(3,'rohit'),(4,'virat');

CREATE TABLE table3(serial int, name varchar,country varchar);

INSERT INTO table3 VALUES(1,'sachin','india'),(20,'bhuvi','india'),(45,'boult','newzealand'),(3,'maxwell','australia'),(45,'rohit','india'),(4,'pant','india'),(10,'KL','india'),(445,'rohit','india');

CREATE TABLE table4(id int, name varchar,rank int);

INSERT INTO table4 VALUES(10,'sachin',1),(45,'rohit',2),(46,'rohit',3),(18,'virat',4),(25,'dhawan',5);

select * from table1 left semi join table2 on table1.name=table2.name where table1.name='rohit' and table2.serial=3;
 id | name  | rank 
----|-------|------
 45 | rohit |    2 
 46 | rohit |    3 
(2 rows)


select * from table1 left anti join table2 on table1.name=table2.name where table1.name='rohit' and table2.serial=3;
 id |  name  | rank 
----|--------|------
 10 | sachin |    1 
 18 | virat  |    4 
 25 | dhawan |    5 
(3 rows)

select * from table1 right semi join table2 on table1.name=table2.name where table1.name='rohit' and table2.serial=3;
 serial | name  
--------|-------
      3 | rohit 
(1 row)

select * from table1 right anti join table2 on table1.name=table2.name where table1.name='rohit' and table2.serial=3;
 serial |  name  
--------|--------
      1 | sachin 
      2 | sachin 
      4 | virat  
(3 rows)

SELECT * FROM table1 t1 LEFT SEMI JOIN table2 t2 on t1.name=t2.name left semi join table3 t3 on t1.name = t3.name left semi join table4 t4 on t1.name=t4.name;
id | name  | rank  
----|-------|------ 
 10 | sachin |   1  
 45 | rohit |    2
 46 | rohit |    3  
(3 rows)

Qualifying Column Names

When two relations of JOIN have the same column name, the relation alias (if any) or relation name must be used for column reference.

SELECT nation.name, region.name FROM nation CROSS JOIN region;
SELECT n.name, r.name FROM nation AS n CROSS JOIN region AS r;
SELECT n.name, r.name FROM nation n CROSS JOIN region r;

Parent topic: HetuEngine DQL SQL Syntax

Previous topic: UNNEST

Next topic: Subqueries

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot