0

I need to efficiently obtain:

players who play more than one world cup and who have never been champion.

primary-key | foreign-key | pk-and-fk

PLAYER<player_id, player_name, country_player, ...>

WORLD<year, winner, ...>

PLAYED_WORLD<player, world_year, ...>

In the first operand of the subtraction I obtain the players with more than 1 world cup no matter if they are champions. In the second operand, I get the players with more than 1 world cup that have already won.

The following query works for me but my idea is to optimize it to be more efficient, since the difference between both operands is nothing more than: WHERE country_player = winner

SELECT *
FROM        (SELECT player_name
        FROM    (SELECT player_id, world_year, player_name, country_player
                FROM    (SELECT player, world_year
                        FROM    (SELECT player
                                FROM played_world
                                GROUP BY player
                                HAVING count(player) > 1) AS playerMoreThan1
                        NATURAL JOIN played_world) AS worldPlayers
                JOIN player ON player= player_id) AS infoWorldPlayers
        JOIN world ON world_year = year) noChampions
EXCEPT      (SELECT player_name
        FROM    (SELECT player_id, world_year, player_name, country_player
                FROM    (SELECT player, world_year
                        FROM    (SELECT player
                                FROM played_world
                                GROUP BY player
                                HAVING count(player) > 1) AS playerMoreThan1
                        NATURAL JOIN played_world) AS worldPlayers
                JOIN player ON player = player_id) AS infoWorldPlayers
        JOIN world ON world_year = year
        WHERE country_player = winner)

Is there a strategy to reuse the queries of the first operand?

If you need the design and the initial load of the db the attached in the publication.

  • 1
    Short answer: Yes, with CTEs. – ypercubeᵀᴹ Jul 20 '18 at 0:30
  • Great, I already found documentation about this (WITH queries), because I was trying to do it with AS haha – CORONEL Braian Jul 20 '18 at 0:42
  • 1
    The naming of the columns is rather confusing ... – ypercubeᵀᴹ Jul 20 '18 at 0:45
  • Or the rename of tables too – CORONEL Braian Jul 20 '18 at 0:47
2

You can use CTEs when you have repeating subqueries. Although it's not necessarily better for performance, in Postgres CTEs are an optimization fence, in other words they are evaluated once (or not at all).

Your query can probably be written more simply though:

SELECT player
FROM played_world AS pw
WHERE NOT EXISTS
      ( SELECT 1
        FROM world AS w
             JOIN player AS p
             ON w.winner = p.country_player
        WHERE w.year = pw.world_year
          AND p.player_id = pw.player
      ) 
GROUP BY player
HAVING count(*) > 1 ;

or with EXCEPT:

SELECT player
FROM played_world
GROUP BY player
HAVING count(*) > 1 

EXCEPT

SELECT p.player_id
FROM world AS w
     JOIN player AS p
     ON w.winner = p.country_player ;
  • I still can not vote for you, but the first solution works :) – CORONEL Braian Jul 20 '18 at 3:55
  • Change c.country_player to p.country_player – CORONEL Braian Jul 20 '18 at 3:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.