kenju's blog

About Programming, Mathematics, Security and Blockchain

SQLでサブクエリを使うときはWITH句で可読性をあげる

SQLを書くときに複雑なサブクエリを書くときには、WITH句を用いると可読性が上がる。 例えば、↓はPostgreSQLの公式サンプル:

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

(retrieved from PostgreSQL: Documentation: 9.1: WITH Queries (Common Table Expressions))

具体例

Before

例えば、以下のようなSQLを書いていたとする。

select *
from
  /* Wikis */
  (select * 
   from Wikis
  ) Wikis
inner join
  /* Blogs */
  (select *
  from Blogs
  ) Blogs
on Wikis.author_id = Blogs.author_id

After

サブクエリ部分は、with句を用いて宣言的に書くことができるので、以下のようにかける。

with

Wikis as (
  select * 
   from Wikis
),

Blogs as (
  select * 
   from Blogs
)

select *
from Blogs
inner join Wikis
on Wikis.author_id = Blogs.author_id