We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Get the Row Count from an Outer Table Join — Vertica Forum

Get the Row Count from an Outer Table Join

Jim_KnicelyJim_Knicely - Select Field - Administrator
edited October 2019 in Tips from the Team

Aggregate functions summarize data over groups of rows from a query result set. When using an aggregate function like COUNT with an "*" or "1" parameter value, you may get a different result when the query implements a LEFT join verses an INNER join.

Example:

dbadmin=> SELECT * FROM t1 ORDER BY 1;
 c
---
 1
 2
 4
(3 rows)

dbadmin=> SELECT * FROM t2 ORDER BY 1;
 c
---
 1
 2
(2 rows)

dbadmin=> SELECT COUNT(*)
dbadmin->   INNER FROM t1
dbadmin->   JOIN t2 ON t1.c = t2.c;
 COUNT
-------
     2
(1 row)

dbadmin=> SELECT COUNT(*)
dbadmin->   FROM t1
dbadmin->   LEFT JOIN t2 ON t1.c = t2.c;
 COUNT
-------
     3
(1 row)

If you want a row count of just the rows from the left outer table that join to the inner table, you have to specify a column name from the lleft outer table in the COUNT function.

dbadmin=> SELECT COUNT(t2.c) t2_count
dbadmin->   FROM t1
dbadmin->   LEFT JOIN t2 ON t1.c = t2.c;
 t2_count
----------
        2
(1 row)

Have fun!

Helpful links:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/AggregateFunctions.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AnalyzingData/Queries/Joins/OuterJoins.htm

Sign In or Register to comment.