JOINS

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.

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

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

• 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;

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

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) 

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;