SQL Query Tester — Test and Run SQL Queries Online
Testing SQL queries typically requires setting up a database, importing data, and connecting a client — significant overhead when you just want to verify a query's logic. The SQL query tester on PublicSoftTools runs queries directly in your browser using an in-memory SQLite database — no installation, no server, no account. Create tables, insert data, and test any SQL query instantly.
SQL Command Categories
| Category | Commands | Example | Purpose |
|---|---|---|---|
| DDL (Data Definition) | CREATE TABLE, ALTER TABLE, DROP TABLE | CREATE TABLE employees (id INT, name TEXT, salary REAL); | Define the structure of database tables |
| DML (Data Manipulation) | INSERT, UPDATE, DELETE | INSERT INTO employees VALUES (1, 'Alice', 50000); | Add, modify, or remove data in tables |
| DQL (Data Query) | SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING | SELECT name, salary FROM employees WHERE salary > 40000; | Retrieve and filter data from tables |
| Joins | INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN | SELECT e.name, d.dept FROM employees e INNER JOIN departments d ON e.dept_id = d.id; | Combine data from multiple related tables |
| Aggregate functions | COUNT(), SUM(), AVG(), MIN(), MAX() | SELECT dept, AVG(salary) FROM employees GROUP BY dept; | Calculate summary statistics over groups of rows |
| Subqueries | Nested SELECT in WHERE, FROM, or SELECT clause | SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); | Use query results as input to another query |
How to Use the SQL Query Tester
- Open the SQL query tester.
- Use the schema panel to create tables with CREATE TABLE statements, or load one of the sample schemas (employees, products, orders).
- Insert data using INSERT INTO statements in the schema panel, or load sample data.
- Write your SQL query in the query editor.
- Click Run (or press Ctrl+Enter) to execute the query.
- Results appear in the table below. Error messages are displayed inline with the query if the SQL is invalid.
- Use the Format button to automatically format your SQL for readability.
SQL JOIN Types Explained
| Join type | Returns | Use case | Example |
|---|---|---|---|
| INNER JOIN | Only rows where the join condition matches in both tables | Most common — find matching records between tables | Employees with a department entry (unassigned employees excluded) |
| LEFT JOIN | All rows from the left table; NULL for unmatched right table columns | Include all records from the primary table regardless of matches | All employees, even those without a department assigned yet |
| RIGHT JOIN | All rows from the right table; NULL for unmatched left table columns | Less common; usually rewritten as a LEFT JOIN with tables swapped | All departments, even those with no employees yet |
| FULL OUTER JOIN | All rows from both tables; NULL where no match exists on either side | Find all records from both tables and see which have no match | All employees and all departments, showing unmatched on both sides |
| CROSS JOIN | Cartesian product — every row in left table combined with every row in right table | Generate all combinations; rarely used in production | All possible employee-project pairings before applying filters |
Writing Your First SQL Query
The basic SELECT query retrieves data from a table:
SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1;
Breakdown:
- SELECT: Which columns to return. Use * for all columns.
- FROM: Which table to query.
- WHERE: Filter rows (optional). Supports =, !=, >, <, LIKE, IN, BETWEEN, IS NULL.
- ORDER BY: Sort results (optional). Add DESC for descending order.
- LIMIT: Return only N rows (optional). Essential for large tables.
To test in the SQL tester: create a table, insert a few rows, then run a SELECT to verify the data and filtering.
Aggregate Queries and GROUP BY
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) calculate a single value from a set of rows. They are used with GROUP BY to calculate per-group statistics:
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees GROUP BY department ORDER BY avg_salary DESC;
Key rules for GROUP BY:
- All columns in SELECT that are not inside aggregate functions must appear in GROUP BY
- HAVING filters after aggregation (equivalent of WHERE for aggregated results): HAVING AVG(salary) > 50000
- WHERE filters before aggregation — it cannot reference aggregate function results
Subqueries
A subquery is a SELECT statement nested inside another query. Subqueries can appear in the WHERE clause, FROM clause, or SELECT clause:
Scalar subquery (returns one value):
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Correlated subquery (references the outer query):
SELECT name FROM employees e WHERE salary = (SELECT MAX(salary) FROM employees WHERE department = e.department);
Correlated subqueries can be slow on large datasets — consider rewriting as a JOIN or using window functions (which SQLite supports from version 3.25+).
Common SQL Errors and Fixes
- "no such table": Table name typo, or table not yet created. Check schema panel for existing tables.
- "ambiguous column name": Multiple joined tables have a column with the same name. Prefix with table alias: e.name, d.name.
- "GROUP BY mismatch": A non-aggregated column in SELECT is not in GROUP BY. Either add it to GROUP BY or wrap in an aggregate.
- Wrong number of rows returned: Check join type. INNER JOIN excludes non-matching rows; LEFT JOIN includes all left-table rows. Also check for unintended duplicates from a CROSS JOIN.
- NULL comparisons: WHERE col = NULL does not work — NULL is not equal to anything, including itself. Use WHERE col IS NULL or WHERE col IS NOT NULL.
SQL Formatting Best Practices
Well-formatted SQL is easier to read, debug, and review. The SQL formatter automatically applies standard formatting. Key conventions:
- SQL keywords in UPPERCASE: SELECT, FROM, WHERE, JOIN, GROUP BY
- Table and column names in lowercase with underscores: employee_name, order_date
- Each clause on its own line with consistent indentation
- Use table aliases consistently: FROM employees e, not sometimes employees.name and sometimes e.name
- End each statement with a semicolon
- Add comments for complex logic: -- This subquery finds the average salary per department
Common Questions
Does the SQL tester support all SQL databases?
The SQL tester uses SQLite, which supports most standard SQL syntax. Some vendor-specific features differ: MySQL uses LIMIT, SQL Server uses TOP, Oracle uses ROWNUM or FETCH FIRST. For subqueries, JOINs, GROUP BY, and most common DML operations, SQLite syntax is compatible with other major databases. Test edge cases against your actual database before production use.
Can I save my queries?
The SQL tester allows you to export your schema and queries. Use the export button to download your SQL as a .sql file, which you can save locally and reimport in a future session. Browser local storage may save recent queries for convenience, but is not a permanent storage solution.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before any aggregation occurs. HAVING filters groups after aggregation. You cannot use aggregate functions (SUM, COUNT, AVG) in a WHERE clause — that's what HAVING is for. Example: WHERE salary > 50000 filters individual employee rows; HAVING AVG(salary) > 50000 filters department groups whose average exceeds 50,000.
Test SQL Queries Online
Create tables, insert data, and run SQL queries instantly in your browser — no database setup, no installation needed.
Open SQL Query Tester