Specifies one or more tables to query data from.
Format
SQL-92 Format:
table-ref [ JoinType ] JOIN table-ref [ ON bool-condition | USING ( ColRef [ , ColRef ]* ) ] ]*
table-ref [ NATURAL [ JoinType ] JOIN table-ref ]*
table-ref [ CROSS JOIN table-ref ]*
SQL-89 Format:
Remarks
A join-expr specifies one or more tables from which data will be extracted.
A join-expr combining two tables creates a join, where data from all tables will be combined and returned in the result set.
In terms of syntax, two styles of join are supported: SQL-92 style, and the older SQL-89 style.
In an SQL-92 style join, the JoinType keyword explicitly defines the type of relationship to establish between two tables. The following table describes the JoinTypes supported:
| Join Type | Returned Rows |
|---|---|
| INNER | All rows from the left hand and right hand table where the constraint clause(s) are true. |
| LEFT OUTER | All rows from the left hand and right hand table where the constraint clause(s) are true plus all rows in the left hand table not matching the constraint clause. |
For INNER and LEFT OUTER join types, the use of NATURAL, ON or USING clauses define the constraint clause to apply. The clauses NATURAL, ON and USING are mutually exclusive, only one can be used per table pair.
- When the NATURAL keyword is used, constraints are automatically added for all columns that are common between the two tables.
- When the USING( ColRef, ... ) syntax is used, one constraints is added for each ColRef. Both tables must contain all columns in the USING list.
- When the ON bool-condition syntax is used, the condition clause is used as the constraint clause.
A CROSS join is, by definition, unconstrained, so the the NATURAL, ON and USING clauses cannot be used.
The JoinType keyword(s) are optional, if omitted the join type defaults to INNER.
The keyword OUTER can be omitted.
The older SQL-89 style join syntax consists of a list of table references separated by commas.
The SQL-89 style join "TableA, TableB" is equivalent to an SQL-92 style join of the form "TableA CROSS JOIN TableB"
The WHERE clause associated with the query can contain clauses that define a constraint relationship between the two or more tables. Constraint clauses effectively convert the join type from a CROSS join into an INNER join.
SELECT RecordTime,Message
FROM CDBEventJournal INNER JOIN CDBPoint ON CDBEventJournal.Source = CDBPoint.FullName
WHERE CDBPoint.AlarmState >= 2
Further Information
An alternative to consider instead of using a JOIN: see de-reference operator.
Restrictions that apply to queries used for GIS Map Markers: see Designing Queries for GIS Map Markers in the Geo SCADA Expert Guide to Mapping and Geographic Information.