Using custom variables in MySQL to write a lazy UNION example

The following is a detailed introduction to the use of custom variables in MySQL and the compilation of a UNION example. If you need a friend, you can refer to this example (refer to self-performance MySQL) and assume that you have the following requirements: write a UNION query. The first subquery is executed as the branch first. If a matched row is found, the query of the second branch is not executed. In general, I

The following is a detailed introduction to the use of custom variables in MySQL and the compilation of a UNION example. If you need a friend, you can refer to this example (refer to self-performance MySQL) and assume that you have the following requirements: write a UNION query. The first subquery is executed as the branch first. If a matched row is found, the query of the second branch is not executed. In general, I

The following describes in detail how to compile a UNION example using custom variables in MySQL. For more information, see

(Refer to < <高性能mysql> >)
Assume that a UNION query is required. The first subquery is run as the branch first. If a matched row is found, the query of the second branch is no longer executed.

In general, we can write such a UNION query:

The Code is as follows:

Select id from users where id = 123456
Union all
Select id from users_archived where id = 123456;

This query can run normally. However, no matter whether a record is found in the users table, it is scanned once in the users_archived table. Therefore, duplicate records may be returned. To reduce unnecessary overhead in this case, the SQL statement can be written as follows:

The Code is 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 preceding query uses the custom variable @ found. by assigning a value in the result column and placing it in the GREATEST function, extra data is not returned. If the first branch query result set is NULL, @ found is still NULL, so the second branch query is executed. In addition, in order not to affect the subsequent traversal results, @ found is reset to NULL at the end of the query.

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