Visual SQL query builder, as productivity tool, to quickly design SELECT read-only queries, in a suggestive and intuitive manner, rather then write their SQL code from scratch. Instantly generates the vendor-specific SQL query and/or executes it directly against the connected relational database, and gets the results. Generic support for dozens of relational database platforms, including Oracle, SQL Server, PostgreSQL, MySQL, Azure, Amazon Redshift, SQLite and Firebird.
Visual Query Builder
Design grids with top diagram area to select table/view/subquery columns from specific shapes:
- Field: start with fields, constants or global function calls. Each grid column becomes a reusable query expression.
- Lookup: alternative display field for any single foreign key. Like department and manager names instead of numeric IDs!
- Formula: fluent contextual transformations of the top selection. We divide here the salary by 10 and convert it to an integer.
- Where: intuitive filter, with string matching patterns, comparisons, list checks.
- Sort: sort order index, with NULLs first or last – available for any platform, natively supported or emulated!
- Format: spreadsheet-like display format, after the query execution.
Instant SQL Query Generator
Can hide diagram area, the design grids (all or just specific builder rows), the result grid or the SQL area. Top SQL button will show you the instantly generated vendor-specific SQL. The query can be instantly simulated for another platform, without having anything from that platform installed!
Result Modes with Drill Down
You may get full instant results while you design your query, on almost each incremental change. The full page Results mode hides anything related to the builder and just executes the query, with result rows returned. Whenever you expose in your query fields involved in relationships (built-in, custom joins or chains), you make possible the in-depth result drill-down, and instantly discover related objects. Click the inner SQL button to visualize the query we transparently generate for each subform.
Group By with Instant Drill Down on All Aggregates
Powerful GROUP BY queries, with extended aggregates like List, Median, Grouping, with native support or emulated! Automatic drill-down to All Aggregates for any row. Show icons in query diagram area, to hide checkboxes and keep only selected columns from each shape.
Inclusive and Exclusive Joins
Our query joins try to hide the relative complexity and confusion related to SQL inner and outer joins. Cross joins are by default established between tables with no connector between. Default connector of an expanded relationship creates an inner join. Inclusive joins – black small circles when you click close to a connector’s end – include all rows from that table. Exclusive joins – empty small circles when you click close to a connector’s end – exclude all rows from that table. Reflexive manager-subordinate relationship here has inclusive joins on both sides. This leads to the inclusion of all rows from both tables. Without this join, the first three rows (with NULL on one side) would not be included in the result.
Generic Union Queries
Union builder grid makes possible concatenation of rows from the main query with other rows of constants or fields, using the UNION, INTERSECT or EXCEPT operators, with optional ALL key. All our database platforms support Intersect and Except, emulated or not! Values may be also used to generate a table of constants, using either VALUES (when supported) or UNION ALL – as in the FruitsU demo query on screen. No tables, but mandatory FROM clause? No worry, we transparently fill all these gaps for you.
Generic Pivot/Cross Tables
Instant pivot/crosstab queries, with vendor-specific native support or emulated (here is an emulated pivot table for MariaDB)! All you need is to turn out a GROUP BY column into a PIVOT column. Automatic or manual column header selections. Enjoy it with other goodies like lookups, format, sort, drill-down on aggregates etc.
Generic Grouping Sets
Instant queries with grouping sets, CUBE or ROLLUP, with vendor-specific native support or emulated (here is an emulated CUBE for MariaDB)! Make a choice from the “cube” button, and Grouping Flag/Fields aggregates can be automatically added. They identify which combination of columns is used for GROUP BY in current row.
One-two clicks is all it takes to further change the layout of your results. Hide Nulls and Hide/Merge Consecutive Duplicate Values buttons bring interesting instant effects on previous query result. You can also show Summary and Highlight builder grids, which remain visible as well in full Results mode. With Summary you can specify grand total-style aggregate functions calculated after the query execution. Highlight will look for cell values according to a simple criteria.
Generic Window Functions
A large number of generated demo queries demonstrate other capabilities of our productivity tool. When supported, you may design window function queries, with ranking and analytic functions, and three specific builders for the OVER clause.