Tips to Write Efficient SQL Queries

Efficient SQL queries retrieve data quickly and reduce unnecessary database operations. By following good practices, we can improve query performance and make database operations easier to maintain.

1. Select Only Required Columns

Use SELECT to retrieve only the columns you need instead of using SELECT *. This reduces unnecessary data retrieval.

Query:

SELECT emp_name, salary
FROM employees;

2. Use WHERE to Filter Records

The WHERE clause retrieves only the records that match a specified condition, reducing the amount of data processed by the query.

Query:

SELECT emp_name, salary
FROM employees
WHERE salary > 50000;

3. Use Indexes on Frequently Searched Columns

Indexes help find records faster. Create indexes on columns often used in WHERE, JOIN and ORDER BY clauses.

Query:

CREATE INDEX idx_emp_name
ON employees(emp_name);

4. Avoid Functions on Indexed Columns in WHERE

Using functions on indexed columns can slow down searches. Compare columns directly with values whenever possible.

Query:

SELECT *
FROM employees
WHERE joining_date >= '2025-01-01'
  AND joining_date < '2026-01-01';

5. Use JOIN Instead of Unnecessary Subqueries

Use JOIN to retrieve related data from multiple tables. It makes queries easier to read, but performance depends on the query and database.

Query:

SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
    ON e.dept_id = d.dept_id;

6. Avoid Using DISTINCT Unnecessarily

The DISTINCT keyword removes duplicate rows from the result. Use it only when unique results are required, as removing duplicates may add processing overhead.

Query:

SELECT DISTINCT dept_id
FROM employees;

7. Use LIMIT to Restrict Results

Use LIMIT to retrieve only the required number of rows and avoid fetching unnecessary data.

Query:

SELECT emp_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

This query returns the five employees with the highest salaries. For consistent results when salaries are equal, add a unique column such as emp_id to the ORDER BY clause.

8. Avoid Leading Wildcards in LIKE

A pattern that starts with % often prevents a standard index from being used for an efficient lookup.

Query:

SELECT *
FROM employees
WHERE emp_name LIKE 'James%';

9. Use EXISTS When Checking for Matching Records

EXISTS checks whether a subquery returns at least one row. It is useful when you only need to know whether a related record exists.

Query:

SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.dept_id
);

10. Avoid Unnecessary Sorting

The ORDER BY clause sorts query results and may require additional processing. Use it only when the results need to appear in a specific order.

Query:

SELECT emp_name, salary
FROM employees;

11. Use EXPLAIN to Analyze Queries

The EXPLAIN statement shows the execution plan chosen by the database. It helps identify table scans, index usage and other potential performance issues.

Query:

EXPLAIN
SELECT emp_name, salary
FROM employees
WHERE salary > 50000;

12. Avoid Fetching Data Repeatedly

Retrieve the required data in a single suitable query when possible instead of running multiple queries to fetch related information one record at a time.

Query:

SELECT emp_id, emp_name, salary
FROM employees
WHERE dept_id = 10;

This can reduce repeated database requests, especially in applications that communicate with a database over a network.