WITH

WITH — defines a Common Table Expression (CTE)

Syntax

WITH { cte_name AS (SELECT query) } [,...]
{ SELECT query
   | Insert statement
   | Replace statement
   | Update statement
   | Delete Statement
}

Description

The WITH clause is used to define and name a CTE.

The CTE is specified after the AS keyword using a SELECT query. With some exceptions (for example, an UPDATE clause), the definition of the CTE can include most of the elements of a SELECT query such as, joins and filtering conditions.

The result of this query is a table and it forms the temporary result set of the CTE. After defining the CTE, it can be used the same way as other (temporary) tables.

Example

# Determine group games of champions league

WITH best_teams_2010 AS
(
         SELECT t.teamid, t.NAME
         FROM teams t
         JOIN (
                SELECT s.year,
                s.teamid,
                SUM(goals) AS goals
                FROM scoring s
                GROUP BY s.year,
                s.teamid
                ) s
         ON t.year=s.year
         AND t.teamid=s.teamid
         WHERE t.year=2010
         ORDER BY wins DESC,
                  ties DESC,
                  goals DESC limit 3
)
SELECT home_team.NAME AS home_team,
       away_team.NAME AS away_team
FROM best_teams_2010 home_team, best_teams_2010 away_team
WHERE home_team.teamid<>away_team.teamid
ORDER BY home_team.NAME,away_team.NAME;

|      HOME_TEAM           AWAY_TEAM
| ------------------- -------------------
| Pittsburgh Penguins San Jose Sharks
| Pittsburgh Penguins Vancouver Canucks
| San Jose Sharks     Pittsburgh Penguins
| San Jose Sharks     Vancouver Canucks
| Vancouver Canucks   Pittsburgh Penguins
| Vancouver Canucks   San Jose Sharks

# Create the hall_of_fame table and insert players from 2010 with the most goals.

CREATE TABLE hall_of_fame
  (
     year INTEGER NOT NULL,
     playerid VARCHAR(10) NOT NULL,
     PRIMARY KEY (year, playerid)
  );

WITH max_goals
     AS (
            SELECT year, MAX(goals) AS goals
            FROM scoring s
            GROUP  BY year
        ),
     player_goals
     AS (
            SELECT year,s.goals,p.playerid
            FROM scoring s
            JOIN players p
            ON s.playerid = p.playerid
        )
INSERT INTO hall_of_fame
SELECT year, playerid
FROM   player_goals pg
WHERE  year = 2010
AND goals IN (
                SELECT goals
                FROM max_goals mg
                WHERE mg.year = pg.year
            );

SELECT *
FROM hall_of_fame;

| YEAR PLAYERID
| ----- ---------
| 2010 perryco01