Using Outer Joins

INFORMATION FROM RELEASE NOTES

Vertica now supports queries that use the ANSI standard INNER JOIN and OUTER JOIN syntax. A query that uses INNER JOIN syntax in the FROM clause produces the same result set as a query that uses the WHERE clause to state the join-predicate. For example, these queries are semantically identical:

SELECT COUNT(*)

FROM Fact_Table INNER JOIN Dim_Table

ON Fact_Table.Product_Key = Dim_Table.Product_Key;

produces the same result set as:

SELECT COUNT(*)

FROM Fact_Table, Dim_Table

WHERE Fact_Table.Product_Key = Dim_Table.Product_Key;

Question for reviewers: does the INNER JOIN syntax require a FOREIGN KEY constraint in the fact table? If not, then it's different from a WHERE clause join and this section should probably be retitled to something like "ANSI Standard Joined Tables."

However, a query that uses OUTER JOIN syntax in the FROM clause returns all rows for which the join-predicate is true, plus one copy of each row in the outer table for which the join-predicate is false. Thus, the result set contains all outer table rows.

When the join-predicate is false, the inner table columns named in the select list are given null values. This includes inner table columns whose constraints would otherwise prevent them from containing null values.

Vertica Restrictions on Outer Joins

1. In Vertica the anchor table must be the outer table in an outer join. For example, both of the following queries are valid and produce identical result sets:

   Question for reviewers: Is "anchor table" or "fact table" the right term to use? In other words, is it necessary to generalize the rule about which table is the outer table in order to cover snowflake joins in which a dimension (unsegmented) table functions as an anchor table?

   SELECT COUNT(*)

   FROM Fact_Table LEFT OUTER JOIN Dim_Table

   ON Fact_Table.Product_Key = Dim_Table.Product_Key

    

   SELECT COUNT(*)

   FROM Dim_Table RIGHT OUTER JOIN Fact_Table

   ON Dim_Table.Product_Key = Fact_Table.Product_Key

2. In Vertica outer join queries require a logical schema definition in which there exists:

   A logical schema consists of a set of tables and referential integrity constraints in a Vertica database. The objects in the logical schema are visible to SQL users. The logical schema does not include projections, which make up the physical schema.

   • a PRIMARY KEY constraint on the dimension table column used in the join predicate
   • no FOREIGN KEY constraint on the fact table column used in the join predicate

   If there is a FOREIGN key constraint on the fact table column, an outer join query produces the same result set as an inner join query. In other words, the result set contains no null-extended rows.

Notes

• Queries can have any number of INNER JOIN or OUTER JOIN clauses. For example:

  SELECT ...

  FROM T1 INNER JOIN (T2 LEFT OUTER JOIN (T3 LEFT OUTER JOIN ...

• If an INNER JOIN or OUTER JOIN query contains a WHERE clause, the WHERE clause cannot contain a join-predicate.

  Question for reviewers: what, if anything, should be said about INNER JOIN and OUTER JOIN queries, pre-join projections, and query performance?

Example

By adding an IS NULL predicate to a dimension table's PRIMARY KEY column in an outer join, it is possible to find data that represents problems. For example, this query finds telephone calls that are not associated with a carrier:

SELECT Call_date,

Call_duration,

Carrier_name

FROM Call_data_records Cdr

LEFT OUTER JOIN Carriers

ON Cdr.Carrier_id = Carriers.Carrier_id

WHERE Carriers.Carrier_id IS NULL;

For More Information

• Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide
• FROM Clause in the SQL Reference Manual

  END OF INFORMATION FROM RELEASE NOTES

Using Outer Joins on Existing Databases

The ALTER TABLE statement allows you to drop a FOREIGN KEY table-constraint from a table that does not have any associated projections. You can then insert rows that would otherwise violate the constraint and use outer join queries to find them.