How can I make my PostgreSQL select query shorter?

Question

I am working on the website and I need to execute a pretty complex select query. I managed to write it, but It looks too long for me and I want to make it shorter. Here is my query:

select friend_id, name, surname, mail, birth_date, address
from (select friend_id, name, surname, mail, birth_date, address, count(friend_id) as rents
      from (select friend.friend_id, name, surname, mail, birth_date, address
            from friend
                     left join profile p on friend.profile_id = p.profile_id
                     left join friend_group_record fgr on friend.friend_id = fgr.friend_id
                     left join meeting m on fgr.friend_group_id = m.friend_group_id
            where m.date between '2019-09-01' and '2020-01-01') as filtered_friend_profiles
      group by friend_id, name, surname, mail, birth_date, address) as counted_friend_profiles
where counted_friend_profiles.rents >= 25;

Does anyone have idea how to make it shorter?

I haven't tested it yet, but it seems this the same query: SELECT f.friend_id, name, surname, mail, birth_date, address FROM friend f LEFT JOIN profile p ON f.profile_id = p.profile_id LEFT JOIN friend_group_record fgr ON f.friend_id = fgr.friend_id LEFT JOIN meeting m ON fgr.friend_group_id = m.friend_group_id WHERE m.date BETWEEN '2019-09-01' AND '2020-01-01' GROUP BY 1, 2, 3, 4, 5, 6 HAVING COUNT(friend_id) >= 25 — Abdel P., Jun 07 '20 at 17:35
Please add table qualification for these columns in the inner SELECT list to make it unambiguous: `name, surname, mail, birth_date, address`. And always disclose your version of Postgres. Ideally, we also need basic table definitions for involved tables showing data types and constraints - even if much can be guessed. — Erwin Brandstetter, Jun 07 '20 at 17:38

score 1 · Answer 1 · edited Jun 07 '20 at 17:38

How about just doing this?

select friend.friend_id, name, surname, mail, birth_date, address
from friend
left join profile p on friend.profile_id = p.profile_id
left join friend_group_record fgr on friend.friend_id = fgr.friend_id
left join meeting m on fgr.friend_group_id = m.friend_group_id
where m.date between '2019-09-01' and '2020-01-01'
group by friend.friend_id, name, surname, mail, birth_date, address
having count(friend.friend_id) >= 25;

See the addition of having clause.

Erwin Brandstetter · Accepted Answer · 2020-06-07T23:06:47.123

Shorter? (And correct?!)

SELECT f.friend_id, name, surname, mail, birth_date, address  -- ⑤
FROM   friend f
JOIN   profile p USING (profile_id)  -- ①
JOIN   friend_group_record fgr ON f.friend_id = fgr.friend_id  -- also USING?
JOIN   meeting m ON fgr.friend_group_id = m.friend_group_id  -- also USING?
WHERE  m.date >= '2019-09-01'  -- ②
AND    m.date <  '2020-01-01'
GROUP  BY 1, 2, 3, 4, 5, 6  -- ③
HAVING count(*) >= 25;  -- ④

① Changed all three instances of LEFT JOIN to [INNER] JOIN, since the condition on the last table in the food chain (meeting) forces all joins to behave like plain joins anyway. (As @wildplasser pointed out.) See:

Also, if column names are identical and distinct among all tables left of the join, USING is convenient short syntax. (Returning only one of each pair of joined columns, which has no bearing on the query at hand.)

Not knowing exact table definitions, I only applied it in the first join, where ambiguities are impossible as there is only a single table left of the join. For persisted queries, generally only advisable if involved column names are stable and distinct over all joined tables.

② Typically, you'd want to exclude the upper bound and BETWEEN is the wrong tool. Related:

③ About this short syntax:

Select first row in each GROUP BY group?

Possibly shorter if there are functional dependencies with PK columns. See:

Why can't I exclude dependent columns from `GROUP BY` when I aggregate by a key?

④ count(*) is shorter & faster (and equivalent in this case). Also, can be used in HAVING clause without listing in SELECT list. See:

Postgres: count(*) vs count(id)

⑤ All source column names should be table-qualified, if only for documentation. Also avoids breakage and confusion from later changes to underlying tables.

Note: `LEFT JOIN meeting m ON fgr.friend_group_id = m.friend_group_id -- also USING? WHERE m.date >= '2019-09-01' ...` is a classic mistake. (and: in this case, it will cascade through all the (LEFT) joins) — wildplasser, Jun 07 '20 at 22:55

How can I make my PostgreSQL select query shorter?

2 Answers2