JOIN

The JOIN statement is used to combine rows from two or more tables.

Description

The JOIN statement is used to combine rows from two or more tables.

The following figure shows seven usages of LEFT JOIN, RIGHT JOIN, INNER JOIN, and OUTER JOIN.

  • LEFT JOIN

SELECT [select_list] FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key

leftjoin

SELECT [select_list] FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key WHERE B.Key IS NULL

leftjoinwhere

  • RIGHT JOIN

SELECT [select_list] FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key

leftjoinwhere

SELECT [select_list] FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key WHERE A.Key IS NULL

leftjoinwhere

  • INNER JOIN

SELECT [select_list] FROM TableA A INNER JOIN TableB B ON A.Key=B.Key

innerjoin

  • FULL JOIN

Note

FULL OUTER JOIN is not supported in MatrixOne and produces a syntax error. FULL JOIN with USING returns INNER JOIN results instead of a true full outer join. To emulate a true full outer join, use the LEFT JOIN + UNION + RIGHT JOIN pattern. For details, see FULL JOIN.

SELECT [select_list] FROM TableA A FULL OUTER JOIN TableB B ON A.Key=B.Key

leftjoin

SELECT [select_list] FROM TableA A FULL OUTER JOIN TableB B ON A.Key=B.Key WHERE A.Key IS NULL OR B.Key IS NULL

fulljoinwhere

For more information, see the reference below: