T-SQL query to check number of existences

Question

I have next approximate tables structure:

accounts:
ID INT,
owner_id INT,
currency_id TINYINT

related to

clients:
ID INT

and

currency_types:
ID TINYINT,
name NVARCHAR(25)

I need to write a stored procedure to check existence of accounts with specific currency and all others, i.e. client can have accounts in specific currency, some other currencies and both.

I have already written this query:

SELECT
    ISNULL((
    SELECT 1
    WHERE EXISTS
    (
        SELECT 1
        FROM [accounts] AS A, [currency_types] AS CT
        WHERE
            A.[owner_id] = @client -- sp param
        AND A.[currency_id] = CT.[ID]
        AND CT.[name] = N'Ruble'
    )), 0) AS [ruble],
    ISNULL((
    SELECT 1
    WHERE EXISTS
    (    
        SELECT A.[ID]
        FROM [accounts] AS A, [currency_types] AS CT
        WHERE
            A.[owner_id] = @client 
        AND A.[currency_id] = CT.[ID]
        AND CT.[name] != N'Ruble'
    )), 0) AS [foreign]

Is it possible to optimize it? I'm new to (T)SQL, so thanks in advance!

Answer 1

Seems pretty reasonable to me, EXISTS will give good performance for this kind of thing.

Do you have a reason for thinking it needs to be optimised? Is it performing badly?

The key thing to ensure is that you have suitable indexes (e.g. on accounts.[owner_id], CT.ID obviously is a PK)

Answer 2

Such query requires one SELECT less:

RETURN ISNULL((SELECT 1
    WHERE
        EXISTS
        (
            SELECT 1
            FROM [accounts] AS A, [currency_types] AS CT
            WHERE
                A.[owner_id] = @client 
            AND A.[currency_id] = CT.[ID]
            AND CT.[name] != N'Ruble'

        )
    ), 0)