Query Builder

The Query Builder writes a SELECT query for you while you click tables and columns. You join tables through their foreign keys, tick the columns you want, and DbSchema shows the SQL as you go. The menus call it the Query Editor. It needs DbSchema Pro.

The Query Builder with customers joined to orders, a filter on status, count(*), the generated SQL at the right and the result grid below

Build a query from a table

A join combines the rows of two tables, such as each customer with their orders. The Query Builder makes the join from the foreign key, so you never type the join condition. A foreign key is a column that points to a row in another table.

  1. Right-click a table on the diagram, such as customers, and choose Query Editor. You can also press Ctrl+B with the table selected.
  2. The builder opens under the diagram. To give it the whole diagram area, double-click its tab.
  3. Click the icon at the right of a key column, such as customer_id. It lists the tables linked to this one.
  4. Choose a table, such as Join orders via orders_customer_id_fkey.
  5. Untick the columns you do not want in the result.
  6. Press Run.

The SQL at the right changes with every click, and the result grid under the builder lists the rows.

The join is an inner join: it keeps only the customers that have orders. To change that, click the join label on the line between the tables and choose Left Join, Full Join, Exists or Not Exists.

If your database has no foreign keys, the builder can follow virtual foreign keys, which exist only in the DbSchema model.

When you close the builder, DbSchema asks whether to save the query to the project. Press Save and Close to keep it, and open it again with Query → Reopen.

Filter the rows

A filter becomes a WHERE condition, such as o.status = 'DELIVERED'.

  1. Right-click a column in the builder and choose Filter.
  2. Choose the operator, such as equals, and type the value.
  3. Press Apply.
The Filter Dialog for the column status, with equals and the value DELIVERED

The filter shows as a new line at the bottom of the table, in italics. To sort the result, use Order Ascending or Order Descending in the same right-click menu.

Count and group rows

An aggregate function sums up many rows into one value, such as the number of orders of each customer.

  1. Right-click a column, such as order_id in orders.
  2. Choose Aggregate, then a function: Min(?), Max(?), Count(*), Count( distinct ?), Avg(?) or Sum(?).
customers joined to orders, with first_name and last_name ticked, and count(*) and the filter status = 'DELIVERED' at the bottom of orders

DbSchema adds a GROUP BY over the other ticked columns. Here, the query counts the delivered orders of each first and last name.

Next steps