Joined Table (SQL)

Help Contents

Introduction

A Joined Table combines data from two tables into a single query result.

Syntax

joined-table ::=
  cross-join
  | qualified-join

cross-join ::=
  table-reference CROSS JOIN table-primary

qualified-join ::=
  table-reference [ join-type ] JOIN table-reference join-specification

join-type ::=
  INNER

join-specification ::=
  ON search-condition | USING ( join-column-list )

join-column-list ::=
  column-name [ { , column-name } ]

Remarks

A CROSS JOIN that does not have a WHERE clause. It creates a cartesian product of the two tables. The number of rows in the query result can be very large. It will be the number of rows in the first table multiplied by the number of rows in the second table. Adding WHERE clause effectively converts the CROSS JOIN into an INNER join.

An INNER JOIN defines a relationship between the two tables. Only rows satisfying the relationship are included in the query result.

Examples

  1. A CROSS JOIN retrieving table names in the 'Core' schema:
    SELECT T.Table_Name,S.Schema_Name
    FROM information_Schema.Tables T CROSS JOIN information_schema.schemata S 
    WHERE S.Schema_Name = 'Core' AND T.Table_Schema = S.Schema_Name
  2. An INNER JOIN to retrieve table names in the 'Core' schema:
    SELECT T.Table_Name,S.Schema_Name
    FROM information_Schema.Tables T 
        INNER JOIN information_schema.schemata S ON (T.table_schema = S.Schema_Name)
    WHERE S.Schema_Name = 'Core'

Further Information

Fernhill SCADA Structured Query Language (SQL)

To learn more about the SQL features in Fernhill SCADA.

Glossary

For the meaning of terms used in Fernhill SCADA.