The lazy UNION example is written in MySQL using custom variables

(refer to the < < High-performance MySQL > > )
Suppose there is a requirement to write an UNION query whose first subquery is executed as a branch, and if a matching row is found, the second branch's query is not executed.

1 in general, we can write an UNION query like this:

select id from users where id=123456
union all
select id from users_archived where id = 123456;

This query can run normally, but regardless of whether the record is found in users table, it will be scanned once in users_archived table. Therefore, duplicate records may also be returned. To reduce unnecessary overhead in this situation, the SQL statement can be written as follows:

SELECT GREATEST(@found := -1, id) AS id, 'users' AS which_tbl
FROM users WHERE id  = 1
UNION ALL
    SELECT id, 'users_archived'
    FROM users_archived WHERE id = 1 and @found IS NULL
UNION ALL
    SELECT 1, 'reset' FROM DUAL WHERE (@found := NULL) IS NOT NLL;

The above query USES the custom variable @found to avoid returning additional data by assigning it once in the result column and placing it in the GREATEST function. If the first branch query result set is NULL, then @found is also NULL, so the second branch query is executed. Also, in order not to affect subsequent traversal results, reset @found to NULL at the end of the query.

In addition, the second column of data is returned to indicate whether the record was queried in the users table or in the users_archived table.