Get the Row Count from an Outer Table Join

Aggregate functions summarize data over groups of rows from a query result set. When using an aggregate function like COUNT with an “*” or “1”…

OpenText profile picture
OpenText

October 22, 20191 minute read

Construction site crane building a blue SQL 3D text. Part of a series.

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 left 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!

Share this post

Share this post to x. Share to linkedin. Mail to
OpenText avatar image

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

See all posts

More from the author

Manutan combines digital services with the human touch to delight customers

Manutan combines digital services with the human touch to delight customers

At Manutan, we equip businesses and communities with the products and services they require to succeed. Headquartered in France, our company has three divisions, serving…

4 minute read

Reaching new markets in Europe and beyond

Reaching new markets in Europe and beyond

How information management specialists at One Fox slashed time to market for innovative products with OpenText Cloud Platform Services At One Fox, we’ve driven some…

4 minute read

SoluSoft helps government agencies tackle fraud faster

SoluSoft helps government agencies tackle fraud faster

Fraud, in all its forms, is a pervasive problem, spanning industries and preying on vulnerabilities in federal and state government systems. Each year in the…

3 minute read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.