SQL Syntax Reference

Comprehensive reference for sql syntax reference covering essential concepts, practical examples, and production best practices.

SQL Syntax Reference is a definitive guide to the core syntax of SQL, focused on the commands, clauses, and idioms engineers use daily in database-driven applications. It assumes familiarity with relational models and provides precise, executable syntax for writing queries, managing schemas, and tuning performance. Use this page when writing complex queries, debugging slow joins, or designing database migrations.

Data Retrieval

Basic SELECT with Filtering and Sorting

SELECT column1, column2, COUNT(*) AS count
FROM table_name
WHERE condition
  AND (subquery) IN (SELECT id FROM another_table)
  AND column LIKE '%pattern%'
  AND date_column BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY column1 ASC, column2 DESC
LIMIT 10 OFFSET 5;

JOINs: Inner, Left, Full, and Self

-- Inner JOIN with filtering and aggregation
SELECT e.name, d.department_name, AVG(s.salary) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
LEFT JOIN salaries s ON e.id = s.emp_id
WHERE d.location = 'San Francisco'
  AND s.year = 2024
GROUP BY e.name, d.department_name
HAVING AVG(s.salary) > 50000
ORDER BY avg_salary DESC
LIMIT 20;

Subqueries and CTEs

-- Scalar subquery in SELECT
SELECT e.name,
       (SELECT AVG(salary) FROM salaries WHERE emp_id = e.id) AS individual_avg
FROM employees e;

-- Correlated subquery in WHERE
SELECT name
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE dept_id = e.dept_id
);

-- Common Table Expression (CTE)
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(revenue) AS total
  FROM sales
  GROUP BY 1
),
top_months AS (
  SELECT month, total,
         RANK() OVER (ORDER BY total DESC) AS rank
  FROM monthly_revenue
)
SELECT month, total
FROM top_months
WHERE rank <= 3;

Data Manipulation

INSERT: Single and Bulk

-- Insert single row
INSERT INTO employees (id, name, dept_id, hire_date)
VALUES (1001, 'Alice Johnson', 3, '2024-03-15');

-- Insert multiple rows
INSERT INTO employees (id, name, dept_id, hire_date)
VALUES
  (1002, 'Bob Smith', 1, '2024-02-01'),
  (1003, 'Carol Lee', 2, '2024-04-10'),
  (1004, 'David Kim', 1, '2024-01-20');

-- Insert from another table
INSERT INTO employees (id, name, dept_id, hire_date)
SELECT id, full_name, department_id, start_date
FROM temp_employees
WHERE status = 'Hired';

UPDATE with Conditional Logic

UPDATE employees
SET salary = salary * 1.1,
    last_raise_date = CURRENT_DATE
WHERE dept_id IN (1, 2)
  AND hire_date < '2020-01-01'
  AND status = 'Active';

-- Update with JOIN
UPDATE employees e
SET salary = e.salary + 5000
FROM bonuses b
WHERE e.id = b.emp_id
  AND b.year = 2024;

-- Update with subquery
UPDATE employees
SET status = 'Promoted'
WHERE id IN (
  SELECT id
  FROM employees
  WHERE salary > (
    SELECT AVG(salary)
    FROM employees
  )
);

DELETE with Conditions and Joins

-- Delete rows based on condition
DELETE FROM employees
WHERE dept_id = 5
  AND hire_date < '2015-01-01'
  AND status = 'Inactive';

-- Delete with JOIN
DELETE FROM employees e
USING departments d
WHERE e.dept_id = d.id
  AND d.name = 'Marketing'
  AND d.active = FALSE;

-- Delete with subquery
DELETE FROM employees
WHERE id IN (
  SELECT id
  FROM employees
  WHERE salary < 15000
    AND dept_id IN (
      SELECT id
      FROM departments
      WHERE location = 'Remote'
    )
);

Schema Management

CREATE TABLE with Constraints

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  sku VARCHAR(50) NOT NULL UNIQUE,
  name TEXT NOT NULL,
  category_id INT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  price DECIMAL(10, 2) DEFAULT 0.00,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  is_active BOOLEAN DEFAULT TRUE,
  CONSTRAINT valid_price CHECK (price >= 0),
  CONSTRAINT unique_sku_per_category
    UNIQUE (sku, category_id)
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_sku ON products(sku) WHERE is_active = TRUE;

ALTER TABLE: Add, Drop, Rename, and Modify

ALTER TABLE employees
ADD COLUMN email VARCHAR(255) UNIQUE,
ADD CONSTRAINT check_email_format
  CHECK (email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'),
DROP COLUMN temp_data,
ALTER COLUMN hire_date TYPE DATE,
ALTER COLUMN hire_date SET NOT NULL,
ALTER COLUMN salary SET DEFAULT 40000,
ALTER COLUMN salary DROP DEFAULT,
DROP CONSTRAINT check_email_format,
RENAME COLUMN salary TO base_salary,
RENAME TO staff;

DROP, TRUNCATE, and RENAME

DROP TABLE IF EXISTS temp_employees CASCADE;
TRUNCATE TABLE employees RESTART IDENTITY;
RENAME TABLE employees TO staff;

Advanced Patterns

Window Functions

SELECT
  name,
  dept_id,
  salary,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS overall_rank,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
  LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary,
  AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
  SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_sum
FROM employees
ORDER BY dept_id, salary DESC;

Conditional Aggregation and Pivoting

SELECT
  dept_id,
  COUNT(*) AS total_employees,
  SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN salary > 70000 THEN 1 ELSE 0 END) AS high_earners,
  AVG(CASE WHEN hire_date >= '2023-01-01' THEN salary ELSE NULL END) AS recent_hire_avg,
  STRING_AGG(name, '; ' ORDER BY hire_date) AS employee_list
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

-- Pivoting with conditional aggregation
SELECT
  dept_id,
  SUM(CASE WHEN role = 'Manager' THEN 1 ELSE 0 END) AS managers,
  SUM(CASE WHEN role = 'Engineer' THEN 1 ELSE 0 END) AS engineers,
  SUM(CASE WHEN role = 'Intern' THEN 1 ELSE 0 END) AS interns
FROM employees
GROUP BY dept_id;

Error Handling and Performance Tips

  INSERT INTO orders (id, customer_id, total)
  VALUES (1001, 200, 99.99)
  ON CONFLICT (id)
  DO UPDATE SET
    total = EXCLUDED.total,
    updated_at = NOW()
  WHERE orders.total < EXCLUDED.total;

This reference captures the precision engineers need to write robust, performant SQL. It reflects real usage patterns, common pitfalls, and subtle distinctions that only emerge when queries fail in production.

This page was rewritten on 10 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.