WITH

WITH statements provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. More about WITH statements can be found here:
PostgreSQL - https://www.postgresql.org/docs/12/queries-with.html
MySQL - https://dev.mysql.com/doc/refman/8.0/en/with.html
MariaDB - https://mariadb.com/kb/en/with

Example

PostgreSQL

removeDiscontinuedOrders := CTE("remove_discontinued_orders")
updateDiscontinuedPrice := CTE("update_discontinued_price")
logDiscontinuedProducts := CTE("log_discontinued")

discontinuedProductID := OrderDetails.ProductID.From(removeDiscontinuedOrders)

stmt := WITH(
    removeDiscontinuedOrders.AS(
        OrderDetails.DELETE().
        WHERE(OrderDetails.ProductID.IN(
                SELECT(Products.ProductID).
                FROM(Products).
                WHERE(Products.Discontinued.EQ(Int(1)))
            ),
        ).RETURNING(OrderDetails.ProductID),
    ),
    updateDiscontinuedPrice.AS(
        Products.UPDATE().
        SET(
            Products.UnitPrice.SET(Float(0.0)),
        ).
        WHERE(Products.ProductID.IN(removeDiscontinuedOrders.SELECT(discontinuedProductID))).
        RETURNING(Products.AllColumns),
    ),
    logDiscontinuedProducts.AS(
        ProductLogs.INSERT(ProductLogs.AllColumns).
        QUERY(SELECT(updateDiscontinuedPrice.AllColumns()).FROM(updateDiscontinuedPrice)).
        RETURNING(ProductLogs.AllColumns),
    ),
)(
    SELECT(logDiscontinuedProducts.AllColumns()).
    FROM(logDiscontinuedProducts),
)

var resp []model.ProductLogs
err = stmt.Query(tx, &resp)

• CTEs appearing in WITH statement have to be declared before WITH statement (removeDiscontinuedOrders, updateDiscontinuedPrice, logDiscontinuedProducts)
• CTEs can be defined as SELECT, INSERT, UPDATE or DELETE statement
• CTE exported columns can be (for better readability) defined before WITH statement (discontinuedProductID)

MySQL and MariaDB

salesRep := CTE("sales_rep")
customerSalesRep := CTE("customer_sales_rep")

salesRepStaffID := Staff.StaffID.From(salesRep)
salesRepFullName := StringColumn("sales_rep_full_name").From(salesRep)

stmt := WITH(
    salesRep.AS(
        SELECT(
            Staff.StaffID,
            Staff.FirstName.CONCAT(Staff.LastName).AS(salesRepFullName.Name()),
        ).FROM(Staff),
    ),
    customerSalesRep.AS(
        SELECT(
            Customer.FirstName.CONCAT(Customer.LastName).AS("customer_name"),
            salesRepFullName,
        ).FROM(
            salesRep.
                INNER_JOIN(Store, Store.ManagerStaffID.EQ(salesRepStaffID)).
                INNER_JOIN(Customer, Customer.StoreID.EQ(Store.StoreID)),
        ),
    ),
)(
    SELECT(customerSalesRep.AllColumns()).
    FROM(customerSalesRep),
)

var dest []model.Customer
err := stmt.Query(db, &dest)

• CTEs appearing in WITH statement have to be declared before WITH statement (salesRep, customerSalesRep)
• MySQL CTEs can be defined only as SELECT statements, but the main statement can be either SELECT, UPDATE or DELETE statement
• MariaDB CTEs can only be SELECT statements
• CTE exported columns can be (for better readability) defined before WITH statement (salesRepStaffID, salesRepFullName)