SQL Query Builder

About SQL Query Builder

This SQL query builder constructs SELECT statements with proper clause ordering and syntax. It supports column selection, table specification, WHERE filtering, ORDER BY sorting, and LIMIT/OFFSET pagination. The tool generates standard SQL compatible with MySQL, PostgreSQL, SQLite, and most relational databases.

It is useful for rapid query prototyping, generating admin panel queries, creating documentation examples, teaching SQL syntax, debugging complex queries, and building starter SQL for applications and reports.

SQL SELECT Statement Structure

A complete SELECT statement follows this clause order:

Standard SELECT Syntax:

SELECT [DISTINCT] column1, column2, ...
FROM table_name
  [JOIN other_table ON condition]
  [WHERE row_filter]
  [GROUP BY column]
  [HAVING group_filter]
  [ORDER BY column [ASC|DESC]]
  [LIMIT n [OFFSET m]]

Clause Execution Order:
1. FROM (including JOINs) - determine source data
2. WHERE - filter individual rows
3. GROUP BY - aggregate into groups
4. HAVING - filter groups
5. SELECT - choose columns
6. ORDER BY - sort results
7. LIMIT/OFFSET - paginate output

Note: This tool builds the core clauses.
JOIN syntax requires manual addition for complex queries.

SELECT Clause Examples

Select all columns:
  SELECT * FROM users;

Select specific columns:
  SELECT id, username, email FROM users;

Select with aliases:
  SELECT
    id AS user_id,
    username AS user_name,
    email AS user_email
  FROM users;

Select with DISTINCT:
  SELECT DISTINCT country FROM customers;

Select with expressions:
  SELECT
    first_name || ' ' || last_name AS full_name,
    age,
    age * 12 AS age_in_months
  FROM users;

Select with aggregate functions:
  SELECT
    COUNT(*) AS total_users,
    AVG(age) AS avg_age,
    MAX(created_at) AS latest_signup
  FROM users;

WHERE Clause Operators

ORDER BY Examples

Single column ascending:
  ORDER BY name ASC

Single column descending:
  ORDER BY created_at DESC

Multiple columns:
  ORDER BY last_name ASC, first_name ASC

Mixed sort directions:
  ORDER BY status DESC, created_at ASC

With expressions:
  ORDER BY price * quantity DESC

With column position (not recommended):
  ORDER BY 2, 1 DESC

NULL handling (PostgreSQL):
  ORDER BY created_at DESC NULLS LAST

NULL handling (MySQL):
  ORDER BY ISNULL(created_at), created_at DESC

LIMIT and OFFSET for Pagination

First 10 rows:
  LIMIT 10

Rows 11-20 (page 2, 10 per page):
  LIMIT 10 OFFSET 10

Rows 21-30 (page 3, 10 per page):
  LIMIT 10 OFFSET 20

Pagination formula (page N, page_size P):
  LIMIT P OFFSET (N-1)*P

Example: Page 5 with 20 items per page
  LIMIT 20 OFFSET 80

PostgreSQL/SQLite alternative syntax:
  LIMIT 10 OFFSET 20
  -- or --
  OFFSET 20 FETCH NEXT 10 ROWS ONLY

SQL Server syntax:
  OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Oracle 12c+ syntax:
  OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

MySQL/PostgreSQL traditional:
  LIMIT 20, 10  -- OFFSET, LIMIT

GROUP BY and Aggregation

GROUP BY groups rows for aggregate calculations:

Count by status:
  SELECT status, COUNT(*) AS count
  FROM orders
  GROUP BY status;

Sum by category:
  SELECT category, SUM(price) AS total
  FROM products
  GROUP BY category;

Multiple aggregates:
  SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
  FROM employees
  GROUP BY department;

Group by multiple columns:
  SELECT
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(total) AS monthly_total
  FROM orders
  GROUP BY YEAR(order_date), MONTH(order_date);

HAVING to filter groups:
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
  HAVING AVG(salary) > 50000;

Complete Query Examples

Example 1: Active Users List
  SELECT id, username, email, created_at
  FROM users
  WHERE status = 'active'
  ORDER BY created_at DESC
  LIMIT 100;

Example 2: Recent Orders with Total
  SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
  FROM orders
  WHERE order_date >= '2024-01-01'
    AND status IN ('pending', 'shipped')
  ORDER BY order_date DESC
  LIMIT 50;

Example 3: Product Sales Summary
  SELECT
    p.category,
    COUNT(*) AS units_sold,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.price) AS revenue
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
  WHERE oi.order_date BETWEEN '2024-01-01' AND '2024-12-31'
  GROUP BY p.category
  HAVING SUM(oi.quantity * oi.price) > 1000
  ORDER BY revenue DESC;

Example 4: User Activity Report
  SELECT
    u.id,
    u.username,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.created_at >= '2023-01-01'
  GROUP BY u.id, u.username
  HAVING COUNT(o.id) > 0
  ORDER BY total_spent DESC
  LIMIT 100;

SQL JOIN Types Reference

SQL Dialect Differences

LIMIT/OFFSET Syntax:
  MySQL/PostgreSQL:  LIMIT 10 OFFSET 20
  PostgreSQL:        LIMIT 10 OFFSET 20
  SQL Server:        OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
  Oracle 12c+:       OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

String Concatenation:
  Standard SQL:      'Hello' || ' ' || 'World'
  MySQL:             CONCAT('Hello', ' ', 'World')
  SQL Server:        'Hello' + ' ' + 'World'

Boolean Values:
  PostgreSQL:        is_active = TRUE
  MySQL:             is_active = 1
  SQL Server:        is_active = 1
  Oracle:            is_active = 1

Date Functions:
  PostgreSQL:        NOW(), CURRENT_DATE
  MySQL:             NOW(), CURDATE()
  SQL Server:        GETDATE(), CAST(GETDATE() AS DATE)
  Oracle:            SYSDATE, TRUNC(SYSDATE)

Top N Records:
  SQL Server:        SELECT TOP 10 * FROM users
  Oracle:            SELECT * FROM users WHERE ROWNUM <= 10
  MySQL/PostgreSQL:  SELECT * FROM users LIMIT 10

Query Optimization Tips

• SELECT specific columns: Avoid SELECT * in production; specify only needed columns to reduce data transfer.
• Use WHERE early: Filter as early as possible to reduce rows processed.
• Index WHERE columns: Columns in WHERE and JOIN conditions should be indexed for large tables.
• Avoid functions on indexed columns: WHERE YEAR(date_col) = 2024 prevents index use; use WHERE date_col >= '2024-01-01' AND date_col < '2025-01-01' instead.
• Use EXISTS for subqueries: WHERE EXISTS (SELECT 1 FROM ...) is often faster than WHERE IN for large datasets.
• Limit OFFSET depth: Deep pagination (OFFSET 10000) is slow; consider keyset pagination (WHERE id > last_seen_id).

Common SQL Patterns