SQL Join Syntax
Inner Join
CONNX supports two types of inner join syntax.
Syntax 1
<table_1.column1> = <table_2.column2>
Example: SELECT * FROM table_1, table_2 WHERE Table_1.key = table_2.key
Syntax 2
<table_1> INNER JOIN <table_2> ON <table_1.column1> = <table_2.column2> [ AND <table_1.column2 = table_2.column3> ...]
Example: SELECT * FROM Table_1 INNER JOIN Table_2 ON Table_1.key = Table_2.key
Outer Join
CONNX supports three types of outer join syntax.
Syntax 1
{oj <table_1> LEFT|RIGHT OUTER JOIN <table_2> ON <table_1.column1> = <table_2.column2> [ AND <table_1.column2 = table_2.column3> ...] }
Example: SELECT * FROM {oj Table_1 LEFT OUTER JOIN Table_2 ON Table_1.key = Table_2.key}
Example: SELECT * FROM {oj Table_1 RIGHT OUTER JOIN Table_2 ON Table_1.key = Table_2.key}
Syntax 2
<table_1.column1> *= <table_2.column2>
Example: SELECT * FROM table_1, table_2 WHERE Table_1.key *= table_2.key
Do not use this syntax for Adabas tables. Instead, use the OUTER JOIN syntax in the example below.
Syntax 3
Use this syntax for Adabas tables.
<table_1> LEFT|RIGHT OUTER JOIN <table_2> ON <table_1.column1> = <table_2.column2> [ AND <table_1.column2 = table_2.column3> ...]
Example: SELECT * FROM Table_1 LEFT OUTER JOIN Table_2 ON Table_1.key = Table_2.key
Example: SELECT * FROM Table_1 RIGHT OUTER JOIN Table_2 ON Table_1.key = Table_2.key