-
Use uppercase for SQL keywords (SELECT, FROM, WHERE, JOIN)
-
Place each major clause on a new line for readability
-
Use meaningful table aliases (e.g., customers AS c not customers AS x)
-
Indent subqueries and nested conditions consistently
-
Align column lists and conditions for visual clarity
SELECT
c.customer_id,
c.customer_name,
o.order_date,
o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
AND o.status = 'completed'
ORDER BY o.order_date DESC;
-
Place most restrictive conditions first in WHERE clauses
-
Use appropriate operators: prefer IN over multiple OR conditions
-
Use EXISTS instead of IN for subqueries when checking existence
-
Avoid functions on indexed columns in WHERE clauses when possible
-
Use parameterized queries to prevent SQL injection
-- Preferred: Use EXISTS for existence checks
SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.order_date > '2024-01-01'
);
-- Avoid: Function on indexed column
WHERE YEAR(order_date) = 2024
-- Preferred: Range comparison
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
-
Always use explicit JOIN syntax instead of implicit joins in WHERE
-
Specify join type explicitly (INNER, LEFT, RIGHT, FULL OUTER)
-
Order joins from largest to smallest table when possible
-
Use appropriate join types based on data requirements
-
Be cautious with CROSS JOINs; ensure they are intentional
-- Explicit join (preferred)
SELECT c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;
-- Avoid implicit join
SELECT c.name, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
-
Use EXPLAIN/EXPLAIN ANALYZE to understand query execution plans
-
Limit result sets with TOP/LIMIT when full results are not needed
-
Use pagination for large result sets
-
Avoid correlated subqueries when possible; use JOINs instead
-
Consider query caching for frequently executed queries
-- Pagination example
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;
-
Filter before grouping when possible (WHERE vs HAVING)
-
Use appropriate aggregate functions (COUNT, SUM, AVG, etc.)
-
Consider window functions for running totals and rankings
-- Efficient: Filter before aggregation
SELECT category_id, COUNT() AS product_count
FROM products
WHERE active = true
GROUP BY category_id
HAVING COUNT() > 10;
-
Keep transactions as short as possible
-
Use appropriate isolation levels for your use case
-
Always include error handling with ROLLBACK
-
Avoid user interaction during open transactions
-
Use savepoints for complex multi-step operations
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
-
Always use parameterized queries or prepared statements
-
Never concatenate user input directly into SQL strings
-
Apply principle of least privilege for database users
-
Audit and log sensitive data access
-
Encrypt sensitive data at rest and in transit
-- Use parameterized queries (pseudo-code)
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @username;
-
Always specify column names explicitly
-
Use bulk inserts for multiple rows when possible
-
Consider using MERGE/UPSERT for insert-or-update scenarios
INSERT INTO customers (customer_name, email, created_at)
VALUES
('John Doe', 'john@example.com', CURRENT_TIMESTAMP),
('Jane Smith', 'jane@example.com', CURRENT_TIMESTAMP);
