DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Migrate, Modernize and Build Java Web Apps on Azure: This live workshop will cover methods to enhance Java application development workflow.

Modern Digital Website Security: Prepare to face any form of malicious web activity and enable your sites to optimally serve your customers.

Kubernetes in the Enterprise: The latest expert insights on scaling, serverless, Kubernetes-powered AI, cluster security, FinOps, and more.

E-Commerce Development Essentials: Considering starting or working on an e-commerce business? Learn how to create a backend that scales.

Related

  • Anatomy of a PostgreSQL Query Plan
  • Data Transfer From SQL Server to Excel
  • SQL Data Manipulation Language (DML) Operations: Insert, Update, Delete
  • Exploring Exciting New Features in Java 17 With Examples

Trending

  • A Roadmap to True Observability
  • Harnessing the Power of APIs: Shaping Product Roadmaps and Elevating User Experiences through Authentication
  • Navigating Software Leadership in a Dynamic Era
  • Build a Philosophy Quote Generator With Vector Search and Astra DB (Part 3)
  1. DZone
  2. Data Engineering
  3. Data
  4. Common Table Expression in ClickHouse

Common Table Expression in ClickHouse

What is Common Table Expression(CTE)? In this post, learn how to use CTE in the ClickHouse database and follow along with use cases with examples.

Taras Baranyuk user avatar by
Taras Baranyuk
DZone Core CORE ·
Jul. 08, 22 · Code Snippet
Like (8)
Save
Tweet
Share
6.9K Views

Join the DZone community and get the full member experience.

Join For Free

It is convenient to use CTE in the following cases:

  • When one request can get data, and its size fits in memory space
  • Multiple uses of the results of this query are required
  • Creating recursive queries

A bonus would be the improved readability of your SQL query.

What is the difference between CTE and temporary tables and nested queries?

  • If the subquery is correlated, then its call is repeated for each row from the selection, dramatically increasing the cost of executing this query.
  • Filling the temporary table with a large amount of data creates a load on the disk.
  • Due to the peculiarities of storing temporary tables, executing queries using them increases the execution time.

Syntax

ClickHouse supports both the WITH <expression> AS <identifier> as well as the WITH <identifier> AS <subquery expression> syntaxes.

  • Initiate a CTE using WITH.
  • Provide a name for a query.
  • Follow with AS.
  • Define the query.
  • If multiple CTEs are required, separate them with a comma.
SQL
 
WITH locations AS
    (
        SELECT location
        FROM table
        WHERE date > (today() - 10)
    )
SELECT *
FROM locations


  • Initiate a CTE using WITH.
  • Define an expression.
  • Follow with AS.
  • Provide a name for the expression.
  • If multiple CTEs are required, separate them with a comma.
SQL
 
WITH ('USA', 'BRA') AS locations
SELECT 'ARG' IN (locations)


Example

Create:

SQL
 
CREATE TABLE SpareParts
(
    `id` UInt32,
    `partName` String,
    `partOrigin` String,
    `storeID` UInt32
)
ENGINE = MergeTree()
ORDER BY id


Insert:

SQL
 
INSERT INTO SpareParts VALUES (1, 'headlight', 'USA', 1)
INSERT INTO SpareParts VALUES (2, 'hood', 'JPN', 1)
INSERT INTO SpareParts VALUES (3, 'bumper', 'USA', 1)
INSERT INTO SpareParts VALUES (4, 'radiator', 'BRA', 3)
INSERT INTO SpareParts VALUES (5, 'wheel', 'BRA', 2)
INSERT INTO SpareParts VALUES (6, 'stabilizer', 'ARG', 3)
INSERT INTO SpareParts VALUES (7, 'absorber', 'TUR', 2)
INSERT INTO SpareParts VALUES (8, 'cable', 'MEX', 1)
INSERT INTO SpareParts VALUES (9, 'spring', 'MEX', 3)
INSERT INTO SpareParts VALUES (10, 'door', 'USA', 2)


Select:

SQL
 
WITH
    originsByStore AS
    (
        SELECT
            storeID,
            groupArray(partOrigin) AS origins
        FROM SpareParts
        GROUP BY storeID
    ),
    partsByStore AS
    (
        SELECT
            storeID,
            groupArray(partName) AS partNames
        FROM SpareParts
        GROUP BY storeID
    ),
    has(origins, 'USA') = 1 AS isUSA
SELECT
    storeID,
    origins,
    partNames,
    isUSA
FROM originsByStore AS t1
LEFT JOIN
(
    SELECT
        storeID,
        partNames
    FROM partsByStore
) AS t2 USING (storeID)


Result:

Result


ClickHouse Data (computing) Execution (computing) Memory (storage engine) sql

Opinions expressed by DZone contributors are their own.

Related

  • Anatomy of a PostgreSQL Query Plan
  • Data Transfer From SQL Server to Excel
  • SQL Data Manipulation Language (DML) Operations: Insert, Update, Delete
  • Exploring Exciting New Features in Java 17 With Examples

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: