Implementing Multi‑Business‑Unit Sales Queries with SQL Row‑Comparison in MySQL

The author, a senior architect, sets up a MySQL 5.7.20 environment and defines a t_ware_sale_statistics table that stores sales metrics such as daily average sales, recent 30/60/90‑day sales, and timestamps.

The business requirement is to obtain sales figures for several business units, each of which may sell multiple products, forming a many‑to‑many relationship; both the list of business IDs and the list of product codes are dynamic.

One straightforward solution is to loop over the business‑unit list in application code and execute a separate query for each unit, but this violates departmental development rules that forbid repeated database calls.

Another approach builds a single SQL statement with many OR conditions using MyBatis dynamic SQL. The query can use indexes, but the resulting statement becomes very long when many business units are involved.

A mixed‑filter method concatenates the business_id and ware_inside_code values into combined strings and filters them after retrieval; although it also uses indexes, the result set contains extra rows that must be filtered out in the application layer.

The chosen solution leverages the SQL‑92 row‑value comparison feature. By matching tuples (business_id, ware_inside_code) against a list of desired pairs in a single indexed query, the database returns exactly the required rows without post‑processing. Example SQL (simplified): SELECT * FROM t_ware_sale_statistics WHERE (business_id, ware_inside_code) IN ((100001,1000),(100001,1001),(100001,1003),(100002,1003),(100002,1004),(100003,1006),(100003,1008),(100003,1009));

The article concludes that row‑comparison is the most suitable method, highlights the importance of evaluating multiple implementations against business constraints, and notes that row‑comparison is a standard feature of the SQL‑92 specification, not a new proprietary extension.