2

I have created an involved CTE, with no recursion, but with multiple selects being done as it processes the business logic of the requirement. Though, I have been advised that the logic needed for the scenario where no rows are returned, should instead to return all rows found from one of the middle CTEs select instead of the final operation.

How can this be achieved?

Note the following example is basic interpretation of my process and the operation in question occurs after multiple cascaded CTE selects are done an essentially chained together.


Example (as found on SQLFiddle)

In a User Table there are two records where the ids of UserIds are 1 and 2. The first CTEs select, TargetUsers will return no rows requiring the the third CTE, ComputeWithUsers, to use the AllUsers instead.

WITH TargetUsers AS
(
  SELECT * 
  FROM Users
  WHERE UserId > 5
)
, AllUsers AS
(
   SELECT * FROM Users
)
, ComputeWithUsers
(
-- This CTE will determine it needs to use `AllUsers` and not `TargetUsers`
)

Or is there a way to make the TargetUsers select smart enough to extract all users when its main operation returns no rows?

  • 1
    Why does it have to be a single query? Can you break it into chunks, e.g. SELECT filtered_set INTO #x; IF @@ROWCOUNT > 0 SELECT FROM #x ELSE SELECT FROM source;? Hopefully the filtered query is typically small or empty, and can use a seek and/or short circuit, but even if not, the worst possible case is two scans. With multiple references to stacked CTEs, the best possible case is (I think) three scans, and it could get worse, depending on the logic you simplified away. Many think a CTE is materialized, but it isn't, so multiple references usually result in multiple table accesses. – Aaron Bertrand Dec 2 '15 at 1:01
  • 1
    Just wanted to add that it seems like an odd requirement... is the user going to know if they got the filtered users or all users? – Dave Dec 2 '15 at 16:17
  • @AaronBertrand It does not have to be a single query. In looking at the answers a temporary table could be used; but this question for me is to understand the limits of CTEs and what processing can/cannot be done. – ΩmegaMan Dec 2 '15 at 17:15
  • @Dave The short answer is no. In the actual code the end consumer is a power user who does not belong to any groups. Each group filters out other non users that don't belong and the result is targeted data per person/group. But because the super user is not tied to a specific group, then they need to see all items. So my example was basic scenario, but it got the idea across; when a select in a CTE does not return data, how does one override it? – ΩmegaMan Dec 2 '15 at 17:18
4

Here is the way I would do this, assuming the "do it all in one query" isn't just a pointless, artificial requirement.

SELECT cols INTO #x FROM dbo.Users WHERE UserID > 5;

IF @@ROWCOUNT > 0
  SELECT cols FROM #x;
ELSE
  SELECT cols FROM dbo.Users;

If the "do it all in one query" is a hard requirement and you can't use a #temp table, or you really have complex logic you've dumbed down from us and/or don't want to repeat, then you're going to have to pay for those choices in performance, I'm afraid. This looks like it should be quite efficient, but there are going to be more hits to the base table than most people would hope / expect:

;WITH TargetUsers AS 
(
  SELECT cols FROM dbo.Users WHERE UserID > 5
),
AllUsers AS 
(
  SELECT cols FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT cols FROM TargetUsers 
  UNION ALL 
  SELECT cols FROM AllUsers
)
...;

In fact if UserID is the primary key and the filter really is on UserID, this might be better as follows (but you'll have to check the plans to see, depends on a variety of factors):

;WITH TargetUserIDs AS 
(
  SELECT UserID FROM dbo.Users WHERE UserID > 5
),
AllUserIDs AS 
(
  SELECT UserID FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT UserID FROM TargetUserIDs 
  UNION ALL 
  SELECT UserID FROM AllUserIDs
)
...
-- and join to dbo.Users to get other columns necessary
-- deferring that to as late as possible

Hopefully your queries are using suboptimal practices due to simplification, and not like that in production, but please give these a read:

  • I ended up using your first suggestion but with a Table variable instead of a temp table. Thanks. – ΩmegaMan Dec 7 '15 at 15:23
  • @OmegaMan Why a table variable? – Aaron Bertrand Dec 7 '15 at 15:26
3

Assuming the performance is acceptable, you can use the following as a means of getting all users when the initial filter fails to return any results.

SELECT *
FROM Users
WHERE UserID > 5
OR NOT EXISTS (SELECT * FROM Users WHERE UserID > 5)

You're just setting a contrived OR that will allow all records to pass the filter if the base select returns nothing.

  • 1
    The OR CASE WHEN NOT EXISTS (SELECT * FROM Users WHERE UserID > 5) THEN 1 ELSE 0 END = 1 can be simpler: OR NOT EXISTS (SELECT * FROM Users WHERE UserID > 5) – ypercubeᵀᴹ Dec 2 '15 at 8:28
  • Yes that does look better... and appears to result in a better execution plan. Updating for anyone that doesn't read comments or Aaron's answer. Thanks! – Dave Dec 2 '15 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.