Tutorial by Examples

These behave in the same manner as nested subqueries but with a different syntax. WITH ReadyCars AS ( SELECT * FROM Cars WHERE Status = 'READY' ) SELECT ID, Model, TotalCost FROM ReadyCars ORDER BY TotalCost; IDModelTotalCost1Ford F-1502002Ford F-150230 Equivalent subquery syntax ...

WITH RECURSIVE ManagersOfJonathon AS ( -- start with this row SELECT * FROM Employees WHERE ID = 4 UNION ALL -- get manager(s) of all previously selected rows SELECT Employees.* FROM Employees JOIN ManagersOfJonathon ON Employees.ID = Manager...

Most databases do not have a native way of generating a series of numbers for ad-hoc use; however, common table expressions can be used with recursion to emulate that type of function. The following example generates a common table expression called Numbers with a column i which has a row for numbe...

WITH RECURSIVE ManagedByJames(Level, ID, FName, LName) AS ( -- start with this row SELECT 1, ID, FName, LName FROM Employees WHERE ID = 1 UNION ALL -- get employees that have any of the previously selected rows as manager SELECT ManagedByJames.Level + 1, ...

Oracle's CONNECT BY functionality provides many useful and nontrivial features that are not built-in when using SQL standard recursive CTEs. This example replicates these features (with a few additions for sake of completeness), using SQL Server syntax. It is most useful for Oracle developers findin...

DECLARE @DateFrom DATETIME = '2016-06-01 06:00' DECLARE @DateTo DATETIME = '2016-07-01 06:00' DECLARE @IntervalDays INT = 7 -- Transition Sequence = Rest & Relax into Day Shift into Night Shift -- RR (Rest & Relax) = 1 -- DS (Day Shift) = 2 -- NS (Night Shift) = 3 ;WITH roster AS ...

Suppose we want to get all product categories with total sales greater than 20. Here is a query without Common Table Expressions: SELECT category.description, sum(product.price) as total_sales FROM sale LEFT JOIN product on sale.product_id = product.id LEFT JOIN category on product.category_id ...

Suppose we want to query the "cheapest products" from the "top categories". Here is an example of query using Common Table Expressions -- all_sales: just a simple SELECT with all the needed JOINS WITH all_sales AS ( SELECT product.price as product_price, category.id a...