how can I shorten a postgresql query that uses same expression multiple times?

Question

I would like to shorten this postgresql query so that I don't need to repeat "coalesce(gl.email, mc.email" several times...

select r.*,l.*, coalesce(r.email, l.email) 
from righttable r full outer join  lefttable l on r.email=l.email 
where coalesce(r.email, l.email) > 'h' 
order by coalesce(r.email, l.email) limit 10

Answer 1

You can join with a using clause:

select *
from righttable r 
full outer join  lefttable l using(email)
where email > 'h' 
order by email 
limit 10

From the documentation:

The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s).

Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

Answer 2

Here is the solution that worked for me...

I repeat this select query consecutively.

select r.<field1>, r.<field2>, ..., l.<field1>, l.<field2>,...,coalesce(r.email, l.email) from righttable r full outer join  lefttable l on r.email=l.email where coalesce(r.email, l.email) > <last_read_email_address> order by coalesce(r.email, l.email) limit 10

In each consecutive request, I set the value of last_read_email_address to the final coalesced email address in the prior result set.

Simple.