SQL Analyzer

SQL Query Examples

Learn from real-world examples of common SQL mistakes, optimizations, and best practices. Click "Try This Example" to analyze any query in the analyzer.

mistakes
medium

SELECT * Performance Issue

Selecting all columns with * is inefficient. Only request the columns you need.

❌ Problem:
SELECT * FROM users WHERE id = 1
✅ Solution:
SELECT id, name, email FROM users WHERE id = 1
mistakes
critical

Missing WHERE in UPDATE

UPDATE without WHERE affects all rows. Always specify which rows to update.

❌ Problem:
UPDATE users SET status = 'active'
✅ Solution:
UPDATE users SET status = 'active' WHERE id = 123
mistakes
high

Function in WHERE Clause

Functions on columns in WHERE prevent index usage. Move logic to the comparison value.

❌ Problem:
SELECT * FROM orders WHERE YEAR(order_date) = 2024
✅ Solution:
SELECT id, total, order_date FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
mistakes
critical

Cartesian Join

Missing join condition creates a Cartesian product, multiplying rows exponentially.

❌ Problem:
SELECT * FROM users, orders
✅ Solution:
SELECT u.id, u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id
mistakes
high

N+1 Query Problem

Subqueries in SELECT execute for each row. Use JOINs with GROUP BY instead.

❌ Problem:
SELECT u.name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count FROM users u
✅ Solution:
SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name
mistakes
medium

Unnecessary DISTINCT

DISTINCT on unique columns (like id) is redundant and adds overhead.

❌ Problem:
SELECT DISTINCT id, name FROM users
✅ Solution:
SELECT id, name FROM users
mistakes
high

LIKE with Leading Wildcard

Leading wildcards in LIKE prevent index usage. Use full-text search for contains queries.

❌ Problem:
SELECT * FROM products WHERE name LIKE '%phone%'
✅ Solution:
SELECT id, name, price FROM products WHERE name LIKE 'phone%'
optimization
medium

Subquery to JOIN Conversion

JOINs are typically faster than subqueries and more readable.

❌ Problem:
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100)
✅ Solution:
SELECT DISTINCT u.id, u.name, u.email FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total > 100
optimization
medium

Using EXISTS vs IN

EXISTS is more efficient than NOT IN and handles NULLs correctly.

❌ Problem:
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders)
✅ Solution:
SELECT u.id, u.name, u.email FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
optimization
high

Proper Index Usage

Avoid functions on indexed columns. Use case-insensitive collation or functional indexes.

❌ Problem:
SELECT * FROM orders WHERE LOWER(email) = 'user@example.com'
✅ Solution:
SELECT id, total, email FROM orders WHERE email = 'user@example.com'
optimization
medium

Query Refactoring with CTE

CTEs improve readability and can be optimized by the query planner.

❌ Problem:
SELECT * FROM (SELECT * FROM users WHERE status = 'active') AS active_users WHERE created_at > '2024-01-01'
✅ Solution:
WITH active_users AS (SELECT id, name, email, created_at FROM users WHERE status = 'active') SELECT * FROM active_users WHERE created_at > '2024-01-01'
optimization
medium

Efficient Pagination

Keyset pagination is faster than OFFSET for large datasets.

❌ Problem:
SELECT * FROM products ORDER BY id OFFSET 10000 LIMIT 20
✅ Solution:
SELECT id, name, price FROM products WHERE id > 10000 ORDER BY id LIMIT 20
best-practices
low

Proper JOIN Syntax

Use explicit JOIN syntax for clarity and to avoid accidental Cartesian products.

❌ Problem:
SELECT * FROM users u, orders o WHERE u.id = o.user_id
✅ Solution:
SELECT u.id, u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id
best-practices
low

Using CTEs Effectively

CTEs make complex queries more readable and maintainable.

❌ Problem:
SELECT u.name, o.total FROM users u JOIN (SELECT user_id, SUM(total) as total FROM orders GROUP BY user_id) o ON u.id = o.user_id
✅ Solution:
WITH order_totals AS (SELECT user_id, SUM(total) as total FROM orders GROUP BY user_id) SELECT u.name, ot.total FROM users u JOIN order_totals ot ON u.id = ot.user_id
best-practices
low

Window Functions

Window functions provide elegant solutions for analytical queries.

❌ Problem:
SELECT u.name, (SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = u.id) FROM users u
✅ Solution:
SELECT u.name, COUNT(o.id) OVER (PARTITION BY u.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id
best-practices
medium

Batch Operations

Batch inserts are much faster than individual INSERT statements.

❌ Problem:
INSERT INTO users (name) VALUES ('Alice'); INSERT INTO users (name) VALUES ('Bob'); INSERT INTO users (name) VALUES ('Charlie');
✅ Solution:
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie')
best-practices
low

Transaction Management

Use transactions to ensure data consistency for related operations.

❌ Problem:
UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2;
✅ Solution:
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
optimization
critical

N+1 Query Pattern

Correlated subquery executes once per row (N+1 problem). Use JOIN with GROUP BY instead.

❌ Problem:
SELECT u.name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS total FROM users u
✅ Solution:
SELECT u.name, COUNT(o.id) AS total FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name
mistakes
high

Invalid GROUP BY Clause

All non-aggregated columns in SELECT must be in GROUP BY clause.

❌ Problem:
SELECT cidade, nome, COUNT(*) FROM clientes GROUP BY cidade
✅ Solution:
SELECT cidade, nome, COUNT(*) FROM clientes GROUP BY cidade, nome
optimization
high

Convert Subquery to JOIN

Subqueries with IN are slower than JOINs. Rewrite for better performance.

❌ Problem:
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid')
✅ Solution:
SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid'
optimization
high

OR Prevents Index Usage

OR with different columns prevents index usage. Use UNION instead.

❌ Problem:
SELECT * FROM users WHERE cidade = 'SP' OR status = 'Ativo'
✅ Solution:
SELECT * FROM users WHERE cidade = 'SP' UNION SELECT * FROM users WHERE status = 'Ativo'
optimization
high

LIKE Leading Wildcard

Leading wildcard in LIKE prevents index usage. Move % to the end if possible.

❌ Problem:
SELECT * FROM users WHERE name LIKE '%Silva'
✅ Solution:
SELECT id, name, email FROM users WHERE name LIKE 'Silva%'
best-practices
low

Window Function Best Practice

Consider if window function should use PARTITION BY to group calculations.

❌ Problem:
SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, name FROM users
✅ Solution:
SELECT ROW_NUMBER() OVER (PARTITION BY cidade ORDER BY id) AS rn, name FROM users