Depending on the database, you may also see:

EXPLAIN ANALYZE

These tools can show how the database plans to execute or actually executes the query.

You may encounter concepts such as: Table scan, Index scan, Index seek, Join strategy, Sort, Aggregate, Estimated rows, Actual rows, Execution cost.

🔹 10. Table Scan vs Index Access

A table scan may require reading a large portion of a table. An index-based access path can sometimes locate matching records much more efficiently.

But: ⚠️ A table scan is not automatically bad.

If you need most of the table, scanning it may actually be the best strategy.

The optimizer chooses based on: Data size + statistics + indexes + query conditions + database engine.

🔹 11. Avoid Unnecessary "DISTINCT"

This query:

SELECT DISTINCT Customer_ID FROM Orders; 

may be perfectly valid.

But don't use DISTINCT simply to hide duplicate rows created by an incorrect join.

🔹 12. Be Careful With JOIN Multiplication

Suppose:

Customer A → 10 Orders and each order has 5 Order Items

Joining customers → orders → order items can produce many rows.

If you then calculate: SUM(Order_Sales) you could accidentally multiply the order-level sales.

This is a logic problem, not just a performance problem. Always understand the grain before joining and aggregating.

🔹 13. "UNION" vs "UNION ALL"

"UNION" removes duplicates.

SELECT Customer_ID FROM Current_Customers 
UNION 
SELECT Customer_ID FROM Previous_Customers;

"UNION ALL" simply combines the results:

SELECT Customer_ID FROM Current_Customers 
UNION ALL 
SELECT Customer_ID FROM Previous_Customers;

If you don't need duplicate removal, UNION ALL is generally preferable because it avoids the additional deduplication work.

🔹 14. Avoid Repeating Expensive Logic

Suppose the same complex calculation appears multiple times.

Instead of repeating it, consider using: CTEs, Derived tables, Temporary tables, Views, Pre-aggregated tables.

The objective is: Calculate expensive logic only when necessary.

🔹 15. CTEs and Performance

CTEs make SQL much easier to organize:

WITH Customer_Sales AS (
    SELECT Customer_ID, SUM(Sales) AS Total_Sales 
    FROM Orders 
    GROUP BY Customer_ID
) 
SELECT * FROM Customer_Sales WHERE Total_Sales > 10000;

A CTE is primarily a query organization technique. Whether it improves performance depends on the database engine, query structure, materialization behavior, and optimizer.

🔹 16. Reduce Data Before Expensive Operations

A useful analytical pattern is:

Raw Data → Filter → Select Required Columns → Join → Aggregate → Window Function → Final Result

The exact optimal order depends on the query, but the principle is: Don't process more data than necessary.

🔹 17. Avoid Correlated Subqueries When a Better Approach Exists

A correlated subquery may conceptually execute in relation to each outer row:

SELECT e.Employee, e.Salary 
FROM Employees e 
WHERE e.Salary > (
    SELECT AVG(e2.Salary) 
    FROM Employees e2 
    WHERE e2.Department = e.Department
);

Sometimes a CTE + join or window function can express the same business logic more efficiently:

WITH Employee_Data AS (
    SELECT 
        Employee, 
        Department, 
        Salary, 
        AVG(Salary) OVER (PARTITION BY Department) AS Avg_Dept_Salary 
    FROM Employees
) 
SELECT Employee, Department, Salary 
FROM Employee_Data 
WHERE Salary > Avg_Dept_Salary;