Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Common Table Expressions Using WITH

Well done!

You have completed Common Table Expressions Using WITH!

Sign up for Treehouse Back to Library

What is a Common Table Expression?

6:31 with Dave McFarland

Learn the basics of common table expressions in SQL: what they are, why they're useful, and the basics of how to create them.

Example Code

Basic Common Table Expression

WITH product_details AS (  
  SELECT ProductName, CategoryName, UnitPrice, UnitsInStock
  FROM Products
  JOIN Categories ON PRODUCTS.CategoryId = Categories.Id
  WHERE Products.Discontinued = 0
)
SELECT * FROM product_details
ORDER BY 2, 1

Basic Common Table Expression Expanded

WITH product_details AS (  
  SELECT ProductName, CategoryName, UnitPrice, UnitsInStock
  FROM Products
  JOIN Categories ON PRODUCTS.CategoryId = Categories.Id
  WHERE Products.Discontinued = 0
)
SELECT CategoryName, COUNT(*) AS unique_product_count, SUM(UnitsInStock) AS stock_count
FROM product_details
GROUP BY 1
ORDER BY 2

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    [SOUND] Writing SQL to discover the information stored in a database is often 0:00
    fun and rewarding. 0:00
    It can also sometimes feel like you're working your way down a rabbit hole. 0:01
    Creating complicated queries, joining multiple tables, and building complex 0:05
    reports with aggregate data often requires a lot of crazy looking SQL. 0:10
    What's worse is having to dig your way through the confusing logic of someone 0:15
    else's query, that includes nested sub queries and lots of joins. 0:20
    In this workshop, 0:25
    I'll teach you how to make your queries easier to read, easier to think about and 0:26

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up