SQL CTE (Common Table Expressions)

WITH RecentCustomers AS (
    SELECT * FROM Customers WHERE age < 30
)
SELECT * FROM RecentCustomers;

CTE With WHERE Clause

CTEs can be used in conjunction with a WHERE clause to filter results in a more structured way. For example,

WITH HighValueOrders AS (
    SELECT * FROM Orders
    WHERE amount > 1000
)
SELECT * FROM HighValueOrders
WHERE customer_id = 3;

Here, the CTE HighValueOrders selects orders with an amount greater than 1000.

Then, the query further filters these to orders made by the customer with ID 3.

Using CTEs With JOIN

CTEs can be effectively used with JOIN to simplify complex joins across multiple tables.

Let's take a look at an example.

WITH CustomerOrders AS (
    SELECT C.customer_id, O.item
    FROM Customers C
    JOIN Orders O ON C.customer_id = O.customer_id
)
SELECT * FROM CustomerOrders;

In this example, the CTE CustomerOrders joins Customers and Orders tables and displays the customer_id column from the Customers table and the item column from the Orders table.

The query then selects all the columns from the CustomerOrders CTE.

Using CTEs With UPDATE Statement

CTEs can also be used within UPDATE statements for updating data based on complex criteria. For example,

WITH PendingShippings AS (
    SELECT * FROM Shippings
    WHERE status = 'Pending'
)
UPDATE Shippings
SET status = 'In Transit'
WHERE shipping_id IN (SELECT shipping_id FROM PendingShippings);

SELECT * FROM Shippings;

Here, the CTE PendingShippings selects all shippings whose status value is Pending.

The UPDATE statement then updates these shippings to In Transit.