What is the SQL With clause (known as CTE) ?

Data System Architecture

About

The with sql clause permits to define inline views (ie without creating them).

These views are also commonly known as CTE (common table expression) because a query (hence a view) returns a logical table.

Example

WITH
  myFirstView AS
  (
    SELECT
      Table1.Code       ,
      Table1.Description,
      Table1.Action     ,
      Table2.Team
    FROM
      Table1,
      Table2
    WHERE
      Table1.Id=Table2.Id
    AND
      Table1.Id <>-1
    AND
      Table1.Id <>-2
  )
  ,
  mySecondView AS
  (
    SELECT distinct Code  FROM myFirstView 
  )
SELECT
  *
FROM
  myFirstView
WHERE
  Code IN
  (
    SELECT Code FROM myFirstView HAVING COUNT(*) > 1 GROUP BY Code
  )
ORDER BY
  Code





Discover More
Data System Architecture
Sql Chaining explained

All operations on a table return a table. A select query returns a table A view returns a table A common table expression returns a table The operations on tables can therefore be chained together...
Data System Architecture
What are Sql Recursive Queries known also as Hierarchical Queries?

A recursive query is a query that is recursive. The general standard format is with recursive This is the equivalent of the generate_series function (Sqlite, PostgreSQL,...



Share this page:
Follow us:
Task Runner