Screens: SQL editor

Result grid

The result area below a SQL tab shows the result of the SQL you ran as a table. Here you filter and sort results, look at values in detail, and copy them to the clipboard. How many rows are fetched and when a filter runs the SQL again is explained in SQL execution and transactions. Editing results is covered in Editing result data, and saving them to a file in Exporting results.

Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects

What's on the screen

No.NameDescription
1Result tabsOne tab per result. The name is the table the result came from; a result from several tables gets a three-dots icon after the name. Hover over a tab to see the SQL that was run. See 'Result tabs' below.
2History tabThe list of SQL run in this SQL tab. It always sits at the end and cannot be closed. See SQL history and saved SQL.
3Result toolsThe refresh button (Reset filters & sort, re-run original query), the row count field, and the Export button.
4Filter/Sort barChooses Client or Server mode and shows the active conditions as chips. The WHERE button and the Clear all button are on the right.
5Search fieldFinds text within the fetched rows (Search loaded rows...). It works separately from the filter.
6Column headerThe type icon, column name, sort indicator, and filter icon. Hover to see the type and the column comment.
7Result tableThe leftmost # column is the row number. NULL appears as a dimmed null, and an empty string as an empty cell. Foreign key columns get an eye icon that opens the referenced data.
8Value panelShows the value of the selected cell in a larger view. See 'Value panel' below.
9Status barThe number of fetched rows, the (truncated) tag and the Load More button when there are more, and the execution time at the right end. Results you can edit also show edit buttons (Editing result data).
  • For statements other than SELECT, the area shows the number of changed rows (row(s) affected) or a completion message and the execution time instead of a table. On error it shows the error message and SQLSTATE.
  • The display format of dates and times and the time zone are set in the Project settingsSQL Editor tab. Headers of time-zone-aware columns show the offset, such as (+09:00).

Result tabs

RunWhere the result goes
Execute buttonReplaces the result tab you are viewing. If that tab is pinned, another unpinned result tab is used, and if there is none, a new tab is created
Run Script buttonCreates one Statistics tab and one result tab per SELECT statement. The tab number is the statement's position in the script
Execution planOpens an Explain tab. See Execution plan
  • The Statistics tab shows the Queries, Updated Rows, Errors, Execute time, Fetch time items. If there are errors, this tab is shown first.
  • If result tabs are open when you click the Run Script button, the Close extra result tabs dialog lets you choose between the Close tabs and Keep tabs buttons. Choose the Don't ask again option to stop being asked. Pinned tabs stay even if you click the Close tabs button.
  • Pin a tab with its pin icon or the Pin tab item in its right-click menu. The right-click menu also has items that close several tabs, such as the Close Others item. Drag tabs to reorder them.
  • When you change the SQL tab's execution target (connection, database, schema), result tabs from the previous target get a warning icon, and the result shows the notice This result came from a different target, so editing is disabled. with the Re-run here button.

Row count and loading more

  1. In the row count field of the result tools, choose how many rows to fetch at a time (the default is the 200 rows option; 500, 1000 and 5000 rows are also available).
  2. Run the SQL. If there are more rows, the status bar shows the (truncated) tag.
  3. Click the Load More button or scroll to the bottom of the table. It fetches the chosen number of additional rows.
  • The Load More button runs the SQL again from the start to receive more rows. If the data changed in the meantime, earlier rows can change too.
  • If you load more while you have edits, the Pending Changes dialog asks whether to discard them.
  • For SQL that is not confirmed to leave the data unchanged when run again (such as a query that advances a sequence through a function), the Load More button is disabled.
  • In the web app, your plan may limit how many rows can be fetched at a time. If the chosen value exceeds the limit, only that many rows are fetched and a notice appears.

Filtering and sorting

The two modes of the Filter/Sort bar differ in what they act on.

Client mode (default)Server mode
TargetThe rows already fetchedThe whole result in the DB
HowFilters and sorts on screenWraps the result in WHERE and ORDER BY and runs the SQL again
SortingEach click on a header cycles ascending, descending, off. Hold Shift while clicking to sort by several columnsSorts by one column
WHERE buttonNot usedAdds the condition you type to the other conditions with AND
  1. Click the filter icon in a column header. A popup for choosing the operator and value opens. The available operators (equals, ranges, IN, LIKE patterns, NOT) depend on whether the column is text, number, date or enum, and the Load DB values button loads the column's values from the DB so you can pick from them.
  2. Click the Apply button. The condition is added to the Filter/Sort bar as a chip.
  3. With two or more conditions, click the AND button on the bar to switch to OR. Remove one condition with the X on its chip, or all of them with the Clear all button.
  • Filtering a truncated result in Client mode filters only the fetched rows. A warning icon tells you so.
  • Server mode can be chosen only for SELECT statements confirmed to be safe to run again.
  • In a result where the same column name appears twice, you cannot filter on that column. Give it an alias (AS) in the SQL.
  • Right-click a column header for the Copy 'status', Sort Ascending, Sort Descending, Filter and Reset (Sort/Filter) items.
  • The refresh button in the result tools clears filters and sorting and runs the original SQL again.

Value panel

The Value panel shows the value of the selected cell larger than the table does. Collapse and expand it with the button in its header; the state is remembered on this device.

  • For JSON values, switch the layout with the Pretty and Minify buttons. HTML, URLs and image URLs are also recognized and shown accordingly.
  • Values over 100 KB show only the beginning, with the Load full content button.
  • Open spatial data with the Map preview button and vectors with the Vector preview button. Binary data cannot be previewed.
  • For an editable cell, change the value in the panel and put it into the table with the Apply button. Cells you cannot edit show the Read-only tag.
  • Double-clicking a cell you cannot edit expands the collapsed panel.

Copying

TaskHowWhat is copied
One cellSelect the cell and press Ctrl+C (Cmd+C on macOS)The value of that cell
As a tableRight-click a cell and choose the Copy, Copy as CSV, or Copy as Markdown table itemThe selected rows (all fetched rows if none are selected) with a header row. The Copy item separates values with tabs, which pastes well into spreadsheets
INSERT statementsRight-click a cell and choose the Copy as INSERT itemOne INSERT statement per selected row. Auto-increment and generated columns are left out. Available only when the result comes from one table
  • Click to select a row, hold Ctrl (Cmd on macOS) and click to add rows, and hold Shift and click to select a range. Clicking the # header selects all rows.
  • Copying covers only the rows fetched to the screen. To get the whole result as a file, use Exporting results.

Masked values

In an account project, if sensitive column masking applies to your role, the values of those columns are hidden before the result reaches you.

MaskingWhat the table shows
Column maskingEvery value appears as ****
Pattern maskingOnly the part of the value that matches the pattern is hidden
  • Masked values stay masked when you copy or export them. The original values never reach the screen.
  • Local projects have no masking. How to set it up is explained in Column masking.

Things to keep in mind

  • Filters, sorting and the Load More button in Server mode run the SQL again. Each run puts load on the DB, and data changed in the meantime appears in the result.
  • Values of long text types such as CLOB arrive in the result table with only their first 1,000 characters. Binary values are replaced by markers such as [BLOB]. If you need the full original values, use Exporting results in Server mode on the desktop app.
  • Result tabs disappear when you close the SQL tab. Export results you want to keep. You can bring executed SQL back into the editor from the History tab.