SQL Query to return one of two select statements

I have a query that us开发者_JAVA百科es a UNION ALL to return results from two different select statements.

I would like to set this up to return only the first select results, and if this first select is empty then return the results of the second select statement.

Can someone give me a simple example of how I might do this.

Thanks in advance Brian

This checks your first query for a record returned, then executes it. Otherwise it executes the second query.

DECLARE @FirstCount int

SET @FirstCount = (SELECT COUNT (*) FROM <First query>)

IF @FirstCount > 0
    BEGIN
        <first query here>
    END
ELSE
    BEGIN
        <Second query here>
    END

You can also use a table variable like so:

DECLARE @FirstQuery TABLE
(
    <fields>
)

SET @FirstQuery = <First Query>

IF EXISTS (SELECT * FROM @FirstQuery)
    BEGIN
        SELECT * FROM @FirstQuery
    END
ELSE
    BEGIN
        <Second query here>
    END

Assuming that you want a query and not a batch, you could duplicate the 1st query of your Union in the second query to determine if it yielded results, and make the 2nd query conditional on that. E.g. only show shipments if there are no order, otherwise show both (makes no business sense, but shows the point):

select id from tb_shipment
union all 
select id from tb_order where 0 = (select COUNT(*) from tb_shipment)

Without knowing your data, I cannot say if there are more efficient ways for you than a WHERE clause.