SQL Tips, Tricks & Best Practices

SQL Tips, Tricks & Best Practices – Writing Clean, Efficient Queries

y now, you’ve learned SQL from basic queries to advanced techniques and real-world projects. In this final blog, we’ll focus on practical tips, tricks, and best practices that help you write efficient, maintainable, and professional-level SQL.

1. Write Readable Queries

Readable queries are easier to debug and maintain:

  • Use uppercase for SQL keywords: SELECT, FROM, WHERE.

  • Use aliases for tables and columns:

SELECT e.first_name, e.last_name, d.department_name
FROM employees AS e
JOIN departments AS d
ON e.department_id = d.department_id;

  • Use indentation for multi-line queries.

**2. Avoid SELECT ***

Selecting only the columns you need improves performance and clarity:

-- Good
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 2;

-- Avoid
SELECT *
FROM employees;

3. Use Indexes Wisely
  • Index columns used in WHERE, JOIN, and ORDER BY clauses.
  • Avoid excessive indexes—they slow down INSERT/UPDATE operations.
  • Regularly maintain indexes in production databases.
4. Use WHERE to Filter Early
Filter data as soon as possible to reduce the rows processed:
SELECT first_name, last_name
FROM employees
WHERE salary > 50000;
  • Reduces workload for joins, aggregations, and sorting.
5. Use CTEs for Complex Queries
  • Common Table Expressions (CTEs) make queries modular and readable.
  • Avoid deeply nested subqueries when a CTE improves clarity:
WITH TopDepartments AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM TopDepartments
WHERE avg_salary > 60000;

6. Comment Your Queries
Adding comments helps others (and your future self) understand your logic:
-- Calculate total sales per region
SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;
7. Use Transactions for Critical Operations
Wrap multiple INSERT/UPDATE/DELETE statements in a transaction to ensure safety:
BEGIN;

UPDATE employees SET salary = salary * 1.10 WHERE department_id = 2;
DELETE FROM employees WHERE department_id = 3;

COMMIT;  -- Use ROLLBACK; if something goes wrong
  • Prevents partial changes that could corrupt data.
8. Optimize Joins and Aggregations
  • Join on indexed columns.
  • Avoid unnecessary tables.
  • Aggregate only what you need with GROUP BY and HAVING.
9. Leverage Built-in Functions
SQL provides many functions for:
  • String operations: CONCAT, SUBSTRING, TRIM.
  • Date operations: DATEADD, DATEDIFF, NOW().
  • Analytics: RANK(), LEAD(), LAG().
Use them to simplify queries and avoid extra processing.
10. Test Queries on Small Data
  • Test logic on a subset of data first.
  • Use LIMIT or TOP to preview results before running large queries.
Following these SQL tips, tricks, and best practices will help you write queries that are:
  • Efficient – fast execution even on large datasets.
  • Readable – easy for others to understand and maintain.
  • Safe – protects against accidental data loss.

Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more