Outer Joins
Outer joins extend the functionality of inner joins by letting you preserve rows of one or both tables that do not have matching rows in the non-preserved table. Outer joins take the following form:
SELECT column‑list FROM left-join-table [ LEFT | RIGHT | FULL ] OUTER JOIN right-join-table ON join-predicate
Omitting the keyword OUTER
from your statements does not affect results of left and right joins. LEFT OUTER JOIN
and LEFT JOIN
perform the same operation and return the same results.
Left Outer Joins
A left outer join returns a complete set of records from the left-joined (preserved) table T1
, with matched records, where available, in the right-joined (non-preserved) table T2
. Where Vertica finds no match, it extends the right side column (T2
) with null values.
=> SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.x = T2.x;
To exclude the non-matched values from T2, write the same left outer join, but filter out the records you don't want from the right side by using a WHERE
clause:
=> SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.x = T2.x WHERE T2.x IS NOT NULL;
The following example uses a left outer join to enrich telephone call detail records with an incomplete numbers dimension. It then filters out results that are known not to be from Massachusetts:
=> SELECT COUNT(*) FROM calls LEFT OUTER JOIN numbers ON calls.to_phone = numbers.phone WHERE NVL(numbers.state, '') <> 'MA';
Right Outer Joins
A right outer join returns a complete set of records from the right-joined (preserved) table, as well as matched values from the left-joined (non-preserved) table. If Vertica finds no matching records from the left-joined table (T1
), NULL
values appears in the T1
column for any records with no matching values in T1
. A right join is, therefore, similar to a left join, except that the treatment of the tables is reversed.
=> SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.x = T2.x;
The above query is equivalent to the following query, where T1 RIGHT OUTER JOIN T2 = T2 LEFT OUTER JOIN T1
.
=> SELECT * FROM T2 LEFT OUTER JOIN T1 ON T2.x = T1.x;
The following example identifies customers who have not placed an order:
=> SELECT customers.customer_id FROM orders RIGHT OUTER JOIN customers ON orders.customer_id = customers.customer_id GROUP BY customers.customer_id HAVING COUNT(orders.customer_id) = 0;
Full Outer Joins
A full outer join returns results for both left and right outer joins. The joined table contains all records from both tables, including nulls (missing matches) from either side of the join. This is useful if you want to see, for example, each employee who is assigned to a particular department and each department that has an employee, but you also want to see all the employees who are not assigned to a particular department, as well as any department that has no employees:
=> SELECT employee_last_name, hire_date FROM employee_dimension emp FULL OUTER JOIN department dept ON emp.employee_key = dept.department_key;
Notes
Vertica also supports joins where the outer (preserved) table or subquery is replicated on more than one node and the inner (non-preserved) table or subquery is segmented across more than one node. For example, in the following query, the fact table, which is almost always segmented, appears on the non-preserved side of the join, and it is allowed:
=> SELECT sales_dollar_amount, transaction_type, customer_name FROM store.store_sales_fact f RIGHT JOIN customer_dimension d ON f.customer_key = d.customer_key; sales_dollar_amount | transaction_type | customer_name ---------------------+------------------+--------------- 252 | purchase | Inistar 363 | purchase | Inistar 510 | purchase | Inistar -276 | return | Foodcorp 252 | purchase | Foodcorp 195 | purchase | Foodcorp 290 | purchase | Foodcorp 222 | purchase | Foodcorp | | Foodgen | | Goldcare (10 rows)