I have a request

SELECT IF ( EXISTS ( SELECT name_short, name_full, ... FROM (SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ...) a UNION ALL SELECT name_short, name_full, ... FROM (SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ...) b ) = 0, 'empty set', 'not empty set' ) ;

It works correctly, and if the query inside the EXISTS function returns an empty string, it displays the message 'empty set ', otherwise the string 'not empty set' . I want to get the values ​​of the name_short and name_full fields if the query inside the EXISTS function returns data (and not an empty string). How can this be done without duplicating the code?

    1 answer 1

    I can offer to do this:

     ( SELECT 1 as 'is_exists' name_short, name_full, /* и так далее */ limit 1 ) union all ( select 0 as 'is_exists', null, null ) limit 1

    But only if you need to get one record. limit 1 required for correct operation in both places. Or like this:

     select (is_exists_table.name_short is not null) as is_exists, name_short, name_full from ( select 1 -- just any one row ) anyrow left join ( select name_short, name_full from /* остальной запрос */ ) is_exists_table ON TRUE

    Returns all records by condition or one record with is_exists = 0 and the other fields are NULL . If name_short can be NULL by itself, then select some other name_short - not null field.