WITH

PostgreSQL

Example

WITH sample AS (
SELECT 'Hello, SQL!' AS Column1
)
SELECT * FROM sample;

Output

 Column1
-----------
 Hello, SQL!
(1 row)

Explanation

In the provided example, the SQL statement consists of a WITH clause, often referred to as a “Common Table Expression” (CTE). The WITH clause creates a temporary table ‘sample’ which contains a single data row with the value ‘Hello, SQL!’. The SELECT statement that follows fetches all the data (in this case, a single data row) from this temporary table.

SQL Server

Example

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
  SELECT SalesPersonID, COUNT(SalesOrderID)
  FROM Sales.SalesOrderHeader
  GROUP BY SalesPersonID
)
SELECT E.EmployeeID, E.FirstName, E.LastName, SC.NumberOfOrders
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS SC
ON E.EmployeeID = SC.SalesPersonID
ORDER BY SC.NumberOfOrders DESC;

Output

EmployeeID | FirstName | LastName | NumberOfOrders
-----------|-----------|----------|---------------
275        | Michael   | Blythe   |  31
276        | Linda     | Mitchell |  29
277        | Jillian   | Carson   |  25
278        | Garrett   | Vargas   |  23

Explanation

The code above is using a Common Table Expression (CTE) named Sales_CTE to calculate the number of orders each salesperson has closed. First, we define our CTE, and after that, we join it with the Employee table to fetch the first name and last name of the employees. The resulting set displays the number of sales orders along with the corresponding employee details. It is ordered by the number of sales orders in descending order, showing the salespersons with the highest number of orders first.

Oracle

Example

WITH temp AS (
  SELECT department_id, COUNT(*) total_employees
  FROM employees
  GROUP BY department_id
)
SELECT e.first_name, e.last_name, t.total_employees
FROM employees e
JOIN temp t ON e.department_id = t.department_id
WHERE e.department_id = 20;

Output

FIRST_NAME | LAST_NAME | TOTAL_EMPLOYEES
-----------|-----------|-----------------
Matthew    | Weiss     | 3
Adam       | Fripp     | 3
Payam      | Kaufling  | 3

Explanation

The WITH keyword is used to create a temporary result set, temp in this case, which is used in the main SELECT query. This result set is defined by the SELECT statement within the parentheses following the WITH keyword.

This temp result set consists of the department IDs and the total number of employees in each department. This temp result is then joined with the employees table, and the query returns the first name, last name, and the total number of employees in the same department for each employee in department ‘20’.