Inner Joins
An inner join combines records from two tables based on a join predicate and requires that each record in the first table has a matching record in the second table. Thus, inner joins return only records from both joined tables that satisfy the join condition. Records that contain no matches are excluded from the result set.
Inner joins take the following form:
SELECT column‑list FROM left-join-table [INNER] JOIN right-join-table ON join-predicate
If you omit the INNER
keyword, Vertica assumes an inner join. Inner joins are commutative and associative. You can specify tables in any order without changing the results.
Example
The following example specifies an inner join between tables store.store_dimension
and public.employee_dimension
whose records have matching values in columns store_region
and employee_region
, respectively:
=> SELECT s.store_region, SUM(e.vacation_days) TotalVacationDays FROM public.employee_dimension e JOIN store.store_dimension s ON s.store_region=e.employee_region GROUP BY s.store_region ORDER BY TotalVacationDays;
This join can also be expressed as follows:
=> SELECT s.store_region, SUM(e.vacation_days) TotalVacationDays FROM public.employee_dimension e, store.store_dimension s WHERE s.store_region=e.employee_region GROUP BY s.store_region ORDER BY TotalVacationDays;
Both queries return the same result set:
store_region | TotalVacationDays --------------+------------------- NorthWest | 23280 SouthWest | 367250 MidWest | 925938 South | 1280468 East | 1952854 West | 2849976 (6 rows)
If the join's inner table store.store_dimension
has any rows with store_region
values that do not match employee_region
values in table public.employee_dimension
, those rows are excluded from the result set. To include that row, you can specify an outer join.