One recent addition to T-SQL is the QUALIFY clause, which filters rows returned by a query based on a search condition that can reference window (analytic) functions [1]. The QUALIFY clause allows filtering directly in the block in which the window function is defined. According to the documentation QUALIFY can be used directly after HAVING clause:
SELECT select_list FROM table_source [ WHERE <search_condition> ] [ GROUP BY group_by_specification ] [ HAVING <search_condition> ] [ QUALIFY <filter_condition> ] [ ORDER BY <order_by_expression> [ , ...n ] ] [ FOR JSON <options> ]
So, for example, if we consider a fictitious Sales Invoices table, to retrieve the customers who bought a product more than once, we had to apply the constraint outside the block in which the window function was defined:
-- product bought more than once by same customer (before) SELECT * FROM (-- calculating resultset SELECT customer_id , product_id , SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity FROM [Sales].[sales_invoices] ) DAT WHERE total_quantity>1
Now, a constraint can be applied directly in the block with the window function via QUALIFY followed by one or more constraints:
-- product bought more than once by same customer (after) SELECT customer_id , product_id , SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity FROM [Sales].[sales_invoices] QUALIFY SUM(quantity) OVER(PARTITION BY invoice_id, product_id)>1
Given the order in which the constraints are processed, QUALIFY allows to reduce redundancy by referencing a new column defined in the SELECT statement (update 19-Oct-2026):
-- product bought more than once by same customer (after) SELECT customer_id , product_id , SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity FROM [Sales].[sales_invoices] QUALIFY total_quantity>1
Hopefully this is available on all supported platforms. Otherwise, the logic must be repeated if a second constraint is needed for checking whether the value is within a boundary. Upon case, the logic can become even more complex (e.g. multiple intervals are checked):
-- product bought more than once by same customer (after) SELECT customer_id , product_id , SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity FROM [Sales].[sales_invoices] QUALIFY SUM(quantity) OVER(PARTITION BY invoice_id, product_id)>1
AND SUM(quantity) OVER(PARTITION BY invoice_id, product_id)<10
Imagine how complex can become a query like the following (update 19-Oct-2026):
-- product bought more than once by same customer with interval testing SELECT customer_id , product_id , SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity FROM [Sales].[sales_invoices] QUALIFY (total_quantity>1 AND total_quantity)<10)
OR (total_quantity>21 AND total_quantity)<30)
OR (total_quantity>41 AND total_quantity)<50)
OR (total_quantity>61 AND total_quantity)<70)
Notes:
1) Having fewer blocks of code improves in theory code's readability and hopefully maintainability when the code doesn't need to be replicated as in the last example. This approach should improve also performance, however further tests are needed to validate this.
2) It's useful to include the same logic used in QUALIFY also in the main SELECT statement, at least for troubleshooting purposes.
3) The syntax seems to be supported only in Fabric warehouse, respectively SQL Endpoints for warehouse, lakehouse and SQL database.4) QUALIFY clause requires at least one window function in the SELECT list, ORDER BY list or QUALIFY expression (see Msg 8826, Level 16).
4) QUALIFY clause requires at least one window function in the SELECT list, ORDER BY list or QUALIFY expression (see Msg 8826, Level 16). (update 19-Oct-2026)
5) One should check also whether the syntax is supported by all the features. For example, it's not supported by UPDATE and DELETE statements in warehouse. The restrictions for SQL Endpoints should be considered as well.
6) Developers must consider also portability of code when it's the case.
7) According to Copilot, QUALIFY is supported in Teradata (originator of the feature), Snowflake, Google BigQuery, Amazon Redshift, Databricks SQL, DuckDB and ClickHouse. (update 19-Oct-2026)
8) Of course, the window functions can be mixed to address different business questions, though it's maybe a good idea to move the DENSE_RANK in SELECT and reference the new column within QUALIFY (update 19-Oct-2026):
-- first/last customer by product incl. total SELECT product_id , SUM(quantity) OVER(PARTITION BY product_id) quantity_total , FIRST_VALUE(customer_id) OVER(PARTITION BY product_id ORDER BY invoice_date ASC) first_status , LAST_VALUE(customer_id) OVER(PARTITION BY product_id ORDER BY invoice_date DESC) last_status FROM [LH_CRM_Data].ERP.[sales_invoices_partitioned4] QUALIFY DENSE_RANK() OVER(PARTITION BY product_id ORDER BY invoice_date)=1
9) QUALIFY can be used also in combination with a CROSS/OUTER APPLY. For example, to retrieve the latest invoice details one could write a query like the one below (update 02-Nov-2026):
-- OUTER APPLY + QUALIFY SELECT top 10 CST.CustomerID , CST.CompanyName , DAT.* FROM [SalesLT].[Customer] CST OUTER APPLY ( SELECT SalesOrderID , [ShipDate] , ROW_NUMBER() OVER (PARTITION BY SOH.CustomerID ORDER BY ShipDate) Ranking FROM [SalesLT].[SalesOrderHeader] SOH WHERE CST.CustomerID = SOH.CustomerID QUALIFY Ranking = 1 ) DAT --WHERE DAT.SalesOrderID IS NOT NULL -- testing ORDER BY CST.CompanyName
It can be surprising that it works (at least I was positively surprised).
Instead of OUTER APPLY one could use a LEFT JOIN (the two versions return the same results):
-- LEFT JOIN + QUALIFY SELECT top 10 CST.CustomerID , CST.CompanyName , DAT.* FROM [SalesLT].[Customer] CST LEFT JOIN ( SELECT SalesOrderID , CustomerID , [ShipDate] , ROW_NUMBER() OVER (PARTITION BY SOH.CustomerID ORDER BY ShipDate) Ranking FROM [SalesLT].[SalesOrderHeader] SOH QUALIFY Ranking = 1 ) DAT ON CST.CustomerID = DAT.CustomerID --WHERE DAT.SalesOrderID IS NOT NULL -- testing ORDER BY CST.CompanyName
Happy coding!
References:
[1] Microsoft Learn (2026) SELECT - QUALIFY clause (Transact-SQL) [link]
