SQL if (exists ()) duplicate queries

Question

I need to write a query that receives a set of information from a table, but if there is no information for this particular client, use the default set. I was wondering if there is a way to avoid duplicate requests in the if (exists ()) statement.

For example:

IF( EXISTS( SELECT * FROM UserTable WHERE Name = @UserName))
BEGIN
    SELECT * FROM UserTable WHERE Name = @UserName))
END
ELSE
BEGIN
    SELECT * FROM UserTable WHERE Name = 'Jon Skeet'))
END

The first two choices (the true part of the if exists) are accurate. I want to avoid executing the same query twice if the statement is true. I know there is a stop when the first true condition is met, but this is still O (n) the worst case.

Another option that I know of is putting information in a temporary table and checking if there is information if it does not return the default information.

, ? ? WHERE, , ?

: . , temp, . , , .

Answer 1

:

SELECT TOP 1
     UserID
FROM
     UserTable
WHERE
     Name IN (@UserName, 'John Skeet')
ORDER BY
     CASE WHEN Name = 'John Skeet' THEN 2 ELSE 1 END

( LIMIT )

:

DECLARE @UserID INT

SELECT
     @UserID = UserID
FROM
     UserTable
WHERE
     Name = @UserName

IF (@UserID IS NULL)
     SELECT
          @UserID = UserID
     FROM
          UserTable
     WHERE
          Name = 'John Skeet'

SELECT @UserID AS UserID

:

SELECT
     COALESCE(T2.UserID, T1.UserID)
FROM
     UserTable T1
LEFT OUTER JOIN UserTable T2 ON
     T2.Name = @UserName
WHERE
     T1.Name = 'John Skeet'

:

SELECT
     UserID
FROM
     UserTable
WHERE
     Name = @UserName

IF (@@ROWCOUNT = 0)  -- MS SQL Server specific, your RDBMS method will vary
     SELECT
          UserID
     FROM
          UserTable
     WHERE
          Name = 'John Skeet'

Answer 2

:

DECLARE @UserID int;
SELECT @UserID = UserID FROM UserTable WHERE Name = @UserName

IF(@UserID IS NULL)
BEGIN
    SELECT @UserID=UserID FROM UserTable WHERE Name = 'Jon Skeet'))
END

SELECT @UserID

Answer 3

, , . get

- :

Declare @uID int;

SELECT @uID = UserID FROM UserTable WHERE Name = @UserName

IF (@uID is null)
begin 
'Select default here'
End

select @uID

Answer 4

, , , , - . , , , - .

, , .., . , - . SQL .

Answer 5

, , Oracle ( , ). , - :

SELECT UserID FROM UserTable WHERE Name = @UserName
IF <no data returned by that> 
BEGIN
    SELECT UserID FROM UserTable WHERE Name = 'John Skeet'
END

Oracle :

BEGIN
    SELECT UserID INTO v_UserID FROM UserTable WHERE Name = :UserName;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    SELECT UserID INTO v_UserID FROM UserTable WHERE Name = 'John Skeet';
END

Answer 6

If you are not going to define a set of variables for each field that you return (and this is only one record), I believe that the most efficient way is to make the call twice. The temp table is likely to be more expensive.