JOIN clause
Note
To see which AWS data source integrations support this SQL command, see Supported OpenSearch SQL commands and functions.
A SQL join is used to combine rows from two relations based on join criteria. The following section describes the overall join syntax and the different types of joins along with examples.
Syntax
relation INNER JOIN relation [ join_criteria ]
Parameters
-
relation
Specifies the relation to be joined.
-
join_type
Specifies the join type.
Syntax:
INNER | CROSS | LEFT OUTER
-
join_criteria
Specifies how the rows from one relation will be combined with the rows of another relation.
Syntax:
ON boolean_expression | USING ( column_name [ , ... ] )
-
boolean_expression
Specifies an expression with a return type of boolean.
Join types
-
Inner Join
The inner join needs to be explicitly specified. It selects rows that have matching values in both relations.
Syntax:
relation INNER JOIN relation [ join_criteria ]
-
Left Join
A left join returns all values from the left relation and the matched values from the right relation, or appends NULL if there is no match. It is also referred to as a left outer join.
Syntax:
relation LEFT OUTER JOIN relation [ join_criteria ]
-
Cross Join
A cross join returns the Cartesian product of two relations.
Syntax:
relation CROSS JOIN relation [ join_criteria ]
Examples
-- Use employee and department tables to demonstrate different type of joins. SELECT * FROM employee; +---+-----+------+ | id| name|deptno| +---+-----+------+ |105|Chloe| 5| |103| Paul| 3| |101| John| 1| |102| Lisa| 2| |104| Evan| 4| |106| Amy| 6| +---+-----+------+ SELECT * FROM department; +------+-----------+ |deptno| deptname| +------+-----------+ | 3|Engineering| | 2| Sales| | 1| Marketing| +------+-----------+ -- Use employee and department tables to demonstrate inner join. SELECT id, name, employee.deptno, deptname FROM employee INNER JOIN department ON employee.deptno = department.deptno; +---+-----+------+-----------| | id| name|deptno| deptname| +---+-----+------+-----------| |103| Paul| 3|Engineering| |101| John| 1| Marketing| |102| Lisa| 2| Sales| +---+-----+------+-----------| -- Use employee and department tables to demonstrate left join. SELECT id, name, employee.deptno, deptname FROM employee LEFT JOIN department ON employee.deptno = department.deptno; +---+-----+------+-----------| | id| name|deptno| deptname| +---+-----+------+-----------| |105|Chloe| 5| NULL| |103| Paul| 3|Engineering| |101| John| 1| Marketing| |102| Lisa| 2| Sales| |104| Evan| 4| NULL| |106| Amy| 6| NULL| +---+-----+------+-----------| -- Use employee and department tables to demonstrate cross join. SELECT id, name, employee.deptno, deptname FROM employee CROSS JOIN department; +---+-----+------+-----------| | id| name|deptno| deptname| +---+-----+------+-----------| |105|Chloe| 5|Engineering| |105|Chloe| 5| Marketing| |105|Chloe| 5| Sales| |103| Paul| 3|Engineering| |103| Paul| 3| Marketing| |103| Paul| 3| Sales| |101| John| 1|Engineering| |101| John| 1| Marketing| |101| John| 1| Sales| |102| Lisa| 2|Engineering| |102| Lisa| 2| Marketing| |102| Lisa| 2| Sales| |104| Evan| 4|Engineering| |104| Evan| 4| Marketing| |104| Evan| 4| Sales| |106| Amy| 4|Engineering| |106| Amy| 4| Marketing| |106| Amy| 4| Sales| +---+-----+------+-----------|