SQL Query Syntax Complete Guide

SQL is the standard language for database interaction. This article covers core SQL query syntax and best practices.

Basic Queries

SELECT Statement

-- Basic query
SELECT * FROM users;

-- Select specific columns
SELECT id, name, email FROM users;

-- Using aliases
SELECT
  first_name AS "First Name",
  last_name AS "Last Name",
  CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- Distinct values
SELECT DISTINCT department FROM employees;

-- Limit results
SELECT * FROM products LIMIT 10;
SELECT * FROM products LIMIT 10 OFFSET 20;  -- Pagination

WHERE Conditions

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 50 AND price <= 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- Logical operators
SELECT * FROM users
WHERE age >= 18 AND status = 'active';

SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing';

SELECT * FROM products
WHERE NOT category = 'electronics';

-- NULL check
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Pattern matching
SELECT * FROM users WHERE name LIKE 'John%';      -- Starts with John
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- Ends with @gmail.com
SELECT * FROM users WHERE name LIKE '%son%';      -- Contains son

-- IN operator
SELECT * FROM products
WHERE category IN ('electronics', 'clothing', 'books');

Sorting and Grouping

-- Sorting
SELECT * FROM products ORDER BY price ASC;       -- Ascending
SELECT * FROM products ORDER BY price DESC;      -- Descending
SELECT * FROM products ORDER BY category, price DESC;  -- Multiple columns

-- Grouping
SELECT category, COUNT(*) as count
FROM products
GROUP BY category;

-- HAVING filters groups
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;

Aggregate Functions

Common Functions

-- Count
SELECT COUNT(*) FROM orders;                     -- Total rows
SELECT COUNT(DISTINCT customer_id) FROM orders;  -- Distinct count

-- Sum and Average
SELECT SUM(amount) as total_sales FROM orders;
SELECT AVG(price) as average_price FROM products;

-- Min and Max
SELECT MAX(price) as highest, MIN(price) as lowest
FROM products;

-- Combined example
SELECT
  category,
  COUNT(*) as product_count,
  AVG(price) as avg_price,
  SUM(stock) as total_stock,
  MIN(price) as min_price,
  MAX(price) as max_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

Window Functions

-- ROW_NUMBER - row number
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- RANK - ranking (same values get same rank, skips)
SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) as rank
FROM students;

-- DENSE_RANK - dense ranking (no skipping)
SELECT
  name,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) as rank
FROM students;

-- LAG / LEAD - previous/next row values
SELECT
  date,
  sales,
  LAG(sales, 1) OVER (ORDER BY date) as prev_day_sales,
  LEAD(sales, 1) OVER (ORDER BY date) as next_day_sales
FROM daily_sales;

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

Join Queries

JOIN Types

SQL JOIN Types:
┌─────────────────────────────────────────────────────┐
│                                                     │
│   INNER JOIN                                        │
│   └── Returns matching rows from both tables       │
│                                                     │
│   LEFT JOIN                                         │
│   └── All left table rows, matching or NULL        │
│                                                     │
│   RIGHT JOIN                                        │
│   └── All right table rows, matching or NULL       │
│                                                     │
│   FULL OUTER JOIN                                   │
│   └── All rows from both, NULL for non-matches     │
│                                                     │
│   CROSS JOIN                                        │
│   └── Cartesian product (all combinations)         │
│                                                     │
└─────────────────────────────────────────────────────┘

JOIN Examples

-- INNER JOIN
SELECT
  orders.id,
  customers.name,
  orders.total
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

-- LEFT JOIN
SELECT
  customers.name,
  COUNT(orders.id) as order_count
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

-- Multiple table join
SELECT
  o.id as order_id,
  c.name as customer_name,
  p.name as product_name,
  oi.quantity,
  oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed';

-- Self join
SELECT
  e.name as employee,
  m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Subqueries

Scalar Subqueries

-- Returns single value
SELECT
  name,
  price,
  price - (SELECT AVG(price) FROM products) as diff_from_avg
FROM products;

-- In WHERE clause
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

Table Subqueries

-- IN subquery
SELECT * FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE total > 1000
);

-- EXISTS subquery
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
  AND o.total > 1000
);

-- FROM subquery
SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
) as dept_salaries
WHERE avg_salary > 50000;

CTE (Common Table Expressions)

-- Basic CTE
WITH high_value_customers AS (
  SELECT customer_id, SUM(total) as total_spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 10000
)
SELECT c.name, hvc.total_spent
FROM customers c
JOIN high_value_customers hvc ON c.id = hvc.customer_id;

-- Recursive CTE (org hierarchy)
WITH RECURSIVE org_tree AS (
  -- Base query: top-level employees
  SELECT id, name, manager_id, 1 as level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive query: subordinates
  SELECT e.id, e.name, e.manager_id, t.level + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;

Data Modification

INSERT

-- Single row insert
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

-- Multiple row insert
INSERT INTO users (name, email, age) VALUES
  ('Alice', 'alice@example.com', 25),
  ('Bob', 'bob@example.com', 28),
  ('Charlie', 'charlie@example.com', 35);

-- Insert from query
INSERT INTO archived_orders
SELECT * FROM orders WHERE created_at < '2024-01-01';

UPDATE

-- Basic update
UPDATE users SET status = 'inactive' WHERE last_login < '2024-01-01';

-- Multiple column update
UPDATE products
SET price = price * 1.1, updated_at = NOW()
WHERE category = 'electronics';

-- Update with JOIN
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.discount = 0.1
WHERE c.membership = 'gold';

DELETE

-- Basic delete
DELETE FROM users WHERE status = 'deleted';

-- Delete with subquery
DELETE FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'inactive'
);

-- Truncate table (faster, no logging)
TRUNCATE TABLE temp_data;

Performance Optimization

Index Usage

-- Create indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(created_at);
CREATE INDEX idx_orders_composite ON orders(customer_id, status);

-- View execution plan
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Optimization Tips

SQL Optimization Best Practices:
┌─────────────────────────────────────────────────────┐
│                                                     │
│   Query Optimization                                │
│   ├── Select only needed columns, avoid SELECT *   │
│   ├── Use EXISTS instead of IN (large datasets)    │
│   ├── Avoid functions on columns in WHERE          │
│   └── Use LIMIT to restrict result sets            │
│                                                     │
│   Index Optimization                                │
│   ├── Create indexes for common query conditions   │
│   ├── Mind column order in composite indexes       │
│   ├── Avoid too many indexes (affects writes)      │
│   └── Regularly analyze and optimize indexes       │
│                                                     │
│   JOIN Optimization                                 │
│   ├── Smaller tables drive larger tables           │
│   ├── Ensure JOIN columns are indexed              │
│   └── Avoid unnecessary JOINs                      │
│                                                     │
└─────────────────────────────────────────────────────┘

Best Practices Summary

Mastering SQL is an essential skill for backend development.