Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ HetuEngine SQL Syntax/ SQL Syntax/ DQL Syntax/ JOINS
Updated on 2022-11-18 GMT+08:00
View PDF

JOINS

Data of multiple relations can be combined.

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;

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.

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');

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)

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: DQL Syntax