Let's discover two tables: bank_accounts and one additional table named transactions.

BankAccounts table

Transactions table

Assume we want to retrieve only the account numbers that have performed at least one transaction.

In this task, we have a query that is not optimized. Your task is to rewrite it in a more optimal way.

Here is a list of the main query rewriting techniques to enhance optimization:

1. Explicitly Specify Columns: Instead of using the asterisk (*) wildcard, explicitly mention column names in queries for better performance, readability, and maintainability;
2. Minimize Subqueries: Reduce the use of subqueries to optimize query performance. Consider alternatives like joins or derived tables to avoid complexity and overhead;
3. Avoid Repeated IN Operators: Limit the use of the IN operator in queries to prevent performance impact. Instead, consider using JOIN or EXISTS clauses for more efficient execution plans;
4. Organize Joins Logically: Start SQL joins with the main table and then join with related tables to optimize query organization and database engine optimization;
5. Use Restrictive WHERE Conditions: Improve query performance by including restrictive conditions in the WHERE clause to filter rows and enhance execution speed;
6. Refactor Code into Stored Procedures or Functions: Encapsulate repetitive code segments into stored procedures or user-defined functions for code reusability, modularity, and easier maintenance. These can reduce redundancy and optimize SQL queries.