SQL for Web Developers: Queries, Joins & Performance Tips

Every web application stores data. Whether you use PostgreSQL, MySQL, SQLite, or SQL Server, you need to speak SQL. Even if you rely on an ORM like Prisma or Sequelize, understanding the SQL it generates is crucial for debugging slow queries and designing efficient schemas.

The Big Four: CRUD Operations

Every database interaction boils down to four operations: Create, Read, Update, Delete.

-- CREATE (Insert)
INSERT INTO users (name, email, role)
VALUES ('Alice', '[email protected]', 'admin');

-- READ (Select)
SELECT id, name, email
FROM users
WHERE role = 'admin'
ORDER BY name ASC
LIMIT 10;

-- UPDATE
UPDATE users
SET role = 'editor', updated_at = NOW()
WHERE id = 42;

-- DELETE
DELETE FROM users
WHERE id = 42;

Understanding JOINs

JOINs combine rows from two or more tables based on a related column. This is the heart of relational databases.

INNER JOIN

Returns only rows that have a match in both tables.

SELECT u.name, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

LEFT JOIN

Returns all rows from the left table, and matched rows from the right table. If no match exists, the right columns are NULL.

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
HAVING COUNT(o.id) = 0;

Indexes: The #1 Performance Tool

An index is like a book's index — it lets the database find rows without scanning every single row in the table (a "full table scan"). Without indexes, a query on a table with 10 million rows must check all 10 million rows.

-- Single-column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

Rule of thumb: Index columns that appear in WHERE, JOIN, and ORDER BY clauses. But don't over-index — each index slows down INSERT and UPDATE operations.

Query Optimization Tips

• Use EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see how the database executes your query. Look for sequential scans on large tables.
• Select only the columns you need. SELECT * fetches every column, including BLOBs and text fields you don't use.
• Use LIMIT for paginated queries instead of fetching all rows.
• Avoid N+1 queries. Instead of querying users, then looping through each user to query their orders, use a JOIN or subquery.
• Use parameterized queries to prevent SQL injection and allow the database to cache query plans.
• Normalize your schema to avoid data duplication, but denormalize read-heavy tables when performance demands it.

Transactions: All or Nothing

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If both succeed:
COMMIT;

-- If anything fails:
ROLLBACK;

Transactions guarantee that a group of operations either all succeed or all fail. This is essential for financial operations, inventory management, and any scenario where partial updates would leave data in an inconsistent state.

Common Mistakes to Avoid

• Not using indexes on columns used in WHERE and JOIN clauses
• Using SELECT * in production queries
• String concatenation in queries — always use parameterized queries to prevent SQL injection
• Missing foreign key constraints — let the database enforce referential integrity
• Not setting appropriate data types — use INTEGER for IDs, TIMESTAMPTZ for dates, TEXT vs VARCHAR appropriately