Which of queries is more efficient? Inner Join vs subquery ? Sum case vs having

Question

I am using PostgreSql 9. I have a simply question. Which of queries is more efficient?

SELECT users_sessions.session_id, users_sessions.series 
FROM users_sessions 
WHERE users_sessions.user_id = 8 
AND users_sessions.session_id IN (
    SELECT session_id 
    FROM sessions_history 
    GROUP BY sessions_history.session_id 
    HAVING SUM(CASE WHEN sessions_history.action = 2 THEN 1 END) = 0
)

VS.

SELECT US8.session_id,Us8.series

FROM

( SELECT us.session_id as S_ID, us.series 
        FROM users_sessions as US
        WHERE US.user_id = 8 ) AS US8
INNER JOIN
(SELECT SH.session_id as SH_ID
        FROM session_history as SH
        WHERE SH.action <> 2) AS SH2    
ON US8.session_id = SH2.session_id

Well, have you tried them? The second one doesn't even seem valid. Test them, write down all the results and then ask one good question. — Jakub Kania, Apr 05 '15 at 09:27
Your version of Postgres? "9" is not a valid version. "9.2" or "9.3" is ... — Erwin Brandstetter, Apr 05 '15 at 09:32
@KamilJ: It is. You cannot reference column aliases from the `SELECT` list ("output columns") in the `HAVING` clause. — Erwin Brandstetter, Apr 05 '15 at 09:39
@KamilJ: Yes. http://stackoverflow.com/questions/19848930/group-by-case-statement/19849537#19849537, http://stackoverflow.com/questions/8119489/postgresql-where-count-condition/8119815#8119815 — Erwin Brandstetter, Apr 05 '15 at 09:51
@KamilJ - Purely commenting on Erwin's point; correct syntax would be `HAVING SUM(1) = 0` *(repeating the logic from the SELECT clause in the HAVING clause)* or if you didn't want to repeat yourself; `SELECT * FROM () AS aggregate WHERE s = 0`. — MatBailie, Apr 05 '15 at 09:58

score 2 · Answer 1 · answered Apr 05 '15 at 09:47

Another solution uses a NOT EXISTS, which is an exact translation of what you seem to ask:

Give me the sessions where no action 2 exists:

SELECT session_id, series 
FROM users_sessions as us
WHERE users_sessions.user_id = 8 
AND NOT EXISTS
 (  SELECT * 
    FROM sessions_history as sh
    WHERE action = 2
    AND us.session_id = sh.session_id 
 )

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

Your 2nd query is was syntactically invalid. You cannot reference column aliases from the SELECT list ("output columns") in the HAVING clause.

Either way, both queries are not good. If you really want to find a combination of (user_id, action) that does not exist, try instead:

SELECT t.*, 0 AS s
FROM  (SELECT 8 AS user_id, 2 AS action) t
LEFT  JOIN (
            users_sessions  us
       JOIN session_history sh USING (session_id)
           ) USING (user_id, action)
WHERE  sh.session_id IS NULL;

I introduce a derived table with a single row in subquery t and LEFT JOIN to the combination of the two base tables. Only returns a row, if the combination does not exist. Assuming the column names user_id and action appear only once in both tables. Else use the more explicit condition for the second join:

ON t.user_id = us.user_id AND t.action = sh.action

Details:

Select rows which are not present in other table

Alternative

Probably faster, yet:

SELECT t.*, 0 AS s
FROM  (SELECT 8 AS user_id, 2 AS action) t
LEFT   JOIN users_sessions  us USING (user_id)
LEFT   JOIN session_history sh USING (action, session_id)
WHERE  sh.session_id IS NULL;

The USING construct is safe either way in this example.

Are you really a fan of `USING`? Doesn't it get really untidy when your queries *also* include range look-ups, etc? — MatBailie, Apr 05 '15 at 10:01
@MatBailie: I have a soft spot for short, concise code. For persisted queries it's safer to use the explicit form to be less vulnerable to later schema changes to the underlying tables. The first `USING` clause in the example is safe, though. — Erwin Brandstetter, Apr 05 '15 at 10:04

MatBailie · Answer 3 · 2015-04-05T10:32:41.563

My tuppence worth;

I can interpret your intentions in two different ways, and have different solutions for each (my preferred solution being first in each case);

Sessions that have at least one History record where the action <> 2
(And not caring how many History records there are where action = 2)

SELECT
    us.session_id,
    us.series
FROM
    users_sessions   AS us
INNER JOIN
    session_history  AS sh
        ON  sh.session_id  = us.session_id
        AND sh.action     <> 2
WHERE
    us.user_id = 8
GROUP BY
    us.session_id,
    us.series

or

SELECT
    us.session_id,
    us.series
FROM
    users_sessions   AS us
WHERE
        us.user_id = 8
    AND EXISTS (SELECT *
                  FROM session_history  AS sh
                 WHERE sh.session_id  = us.session_id
                   AND sh.action     <> 2
               )

or

SELECT
    us.session_id,
    us.series
FROM
    users_sessions   AS us
INNER JOIN
(
    SELECT
        session_id
    FROM
        session_history
    WHERE
        sh.action <> 2
    GROUP BY
        session_id
)
    AS sh
        ON  sh.session_id  = us.session_id
WHERE
    us.user_id = 8

Sessions that have no History records where action = 2
(but can have other History records)

SELECT
    us.session_id,
    us.series
FROM
    users_sessions   AS us
LEFT JOIN
    session_history  AS sh
        ON  sh.session_id = us.session_id
        AND sh.action     = 2
WHERE
        us.user_id     = 8
    AND sh.session_id IS NULL

-- No GROUP BY needed this time

or

SELECT
    us.session_id,
    us.series
FROM
    users_sessions   AS us
WHERE
        us.user_id = 8
    AND NOT EXISTS (SELECT *
                     FROM session_history  AS sh
                     WHERE sh.session_id = us.session_id
                       AND sh.action     = 2
                   )

score 0 · Answer 4 · answered Apr 05 '15 at 10:03

0

What do you think about this?

SELECT US8.session_id,Us8.series

FROM

( SELECT us.session_id as S_ID, us.series 
        FROM users_sessions as US
        WHERE US.user_id = 8 ) AS US8
INNER JOIN
(SELECT SH.session_id as SH_ID
        FROM session_history as SH
        WHERE SH.action <> 2) AS SH2    
ON US8.session_id = SH2.session_id

answered Apr 05 '15 at 10:03

KamilJ

249
3
5
14

Is it possible for a `session_id` to have Zero corresponding rows in `session_history`? If so you need a `LEFT OUTER JOIN` or `NOT EXISTS` type of look-up, as per the other answers here. – MatBailie Apr 05 '15 at 10:06
You would first need to define *exactly* (in the question) what the query is supposed to do. There a quite a few fine points here. – Erwin Brandstetter Apr 05 '15 at 10:06
Is is possible for a `session_id` to have Multiple corresponding rows in `session_history`? If so, your `SH2` sub-query should have a `DISTINCT` or `GROUP BY` to prevent duplication of results. – MatBailie Apr 05 '15 at 10:07

Which of queries is more efficient? Inner Join vs subquery ? Sum case vs having

4 Answers4

Alternative