SQL Query Tester — Write and Test SQL Online
SQL is the most widely used data query language in software — and most developers learn it by actually writing queries, not reading about them. This guide covers the core SQL clauses, joins, aggregations, and subqueries, with the free browser-based SQL query tester for instant feedback.
SQL Clause Execution Order
SQL queries are written in one order but executed in another. Understanding this prevents common mistakes with column aliases and WHERE vs HAVING.
| Step | Clause | What it does |
|---|---|---|
| 1 | FROM / JOIN | Identifies the source tables |
| 2 | WHERE | Filters rows before grouping |
| 3 | GROUP BY | Groups rows for aggregation |
| 4 | HAVING | Filters groups after aggregation |
| 5 | SELECT | Selects and transforms columns |
| 6 | DISTINCT | Removes duplicate rows |
| 7 | ORDER BY | Sorts the result set |
| 8 | LIMIT / OFFSET | Truncates rows for pagination |
How to Use the SQL Query Tester
- Open the SQL Query Tester
- Browse the built-in sample tables — employees, departments, sales — using the schema panel
- Write your query in the editor and click Run
- Results appear in the table below; errors show with line information
- Use Format to auto-indent your query for readability
JOIN Types Reference
| JOIN type | Returns | Common use |
|---|---|---|
| INNER JOIN | Matching rows in both tables | Most common — related records only |
| LEFT JOIN | All left rows + matched right | Optional relationships — nulls for unmatched |
| RIGHT JOIN | All right rows + matched left | Mirror of LEFT JOIN |
| FULL OUTER JOIN | All rows from both tables | Audit mismatches between two tables |
| CROSS JOIN | Cartesian product | Generate combinations (use with care) |
| SELF JOIN | Table joined to itself | Hierarchies — employees and their managers |
Advanced SQL Patterns
Aggregation with GROUP BY and HAVING
WHERE filters rows before aggregation; HAVING filters after. To find departments with more than 5 employees: SELECT dept, COUNT(*) AS n FROM employees GROUP BY dept HAVING COUNT(*) > 5. You cannot use the alias n in HAVING because SELECT runs after HAVING.
Correlated subqueries
A correlated subquery references the outer query and re-runs for each row. To find employees earning above their department's average: SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept). Correlated subqueries are powerful but can be slow — often rewritable with a JOIN to a derived table.
Window functions
Window functions compute values across a set of rows related to the current row without collapsing them into groups. ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) ranks employees within each department. Unlike GROUP BY, all original rows remain in the result. Other useful window functions: RANK, DENSE_RANK, LAG, LEAD, SUM/AVG with OVER.
CTEs for readable queries
Common Table Expressions (WITH clauses) let you name intermediate result sets and reference them like temporary tables. This breaks complex queries into readable steps without nesting subqueries four levels deep. Recursive CTEs can traverse hierarchical data (org charts, bill of materials) that would otherwise require procedural code.
Common Mistakes
NULL comparisons
NULL is not equal to anything — including itself. WHERE col = NULL never matches any row. Use WHERE col IS NULL or IS NOT NULL. NULL propagates through arithmetic — NULL + 5 = NULL — so aggregate functions like SUM and AVG silently ignore NULLs.
Implicit GROUP BY errors
In strict SQL (and most production databases), every column in SELECT that is not inside an aggregate function must appear in GROUP BY. Selecting name while grouping by dept is an error — unless you want MAX(name) or some other aggregation.
DISTINCT vs GROUP BY performance
DISTINCT and GROUP BY often produce the same result for de-duplication, but GROUP BY is frequently faster because it allows the query planner to use grouping indexes. DISTINCT forces a sort on all selected columns. For pure de-duplication with no aggregation, benchmark both on your specific dataset.
Test Your SQL Queries Online
Write and execute SQL instantly in the SQL Query Tester — SELECT, JOIN, GROUP BY, and subqueries against a built-in dataset, no setup required.
Open SQL Query Tester