Learn more about Toad for SQL Server
at Quest.com

Find solutions and downloads at the
Toad for SQL Server Support Portal

< To bookmark a page, right-click the Page Title and select Add to Favorites / Bookmark This Page

Toad for SQL Server 5.7

You are here: Build Queries Visually > Build Queries Visually

 

Build Queries Visually

The Query Builder enables you to create a query without writing or editing SQL statements. Even if you are familiar with SQL, the graphical interface makes it easier to create relationships and visualize the query.

To build a query

  1. Click (ALT+T+Q).

  2. Drag tables and views from the Object Explorer to the Diagram pane.

  3. Join columns by selecting a column in a table and dragging it to a column in another table. A connector line displays between the two objects to visually represent the relationship.

    Note: By default, all joins are initially created as Inner Joins. Double-click to modify the join type. See Join Columns for more information.

  4. Add columns to the query using one of the following methods:

    The selected columns display in the Criteria pane in the bottom portion of the Query Diagram window (see the following image).

    Notes:

  5. Select the type of statement you want to create (default is Select Statement):

    Notes:

  1. Use the Criteria pane (bottom portion of the Query Diagram window) to specify the query options. Review the following for additional information:

    Only fetch unique records

    Select this checkbox to eliminate duplicate records from query results.

    Top row count

    Enter the number of records that you want to return that have the highest values.

    You can also select the Percent checkbox to select a percentage of records to return instead of an exact value.

    Aggregate Function

    Select one of the following functions to calculate column values:

    • Avg—Returns the average of the values in a group.

    • Avg (Distinct)—Returns the average of the unique values in a group.

    • Count—Returns the total number of items in a column. This function does not ignore NULL values when calculating results.

    • Count (Distinct)—Returns the total number of unique values in a column.

    • Max—Returns the maximum value for the column.

    • Min—Returns the minimum value for the column.

    • Stddev—Returns the sample standard deviation of a numeric expression evaluated over a set.

    • Sum—Returns the sum of all the values in the expression.

    • Sum (Distinct)—Returns the sum of all the unique values in the expression.

    Note: If you add a Group By clause, you must specify an aggregate function for any remaining columns.

    Where Condition

    Use to compose a Where clause or to add a subquery.

    1. Select the Where field in the column and click .
    2. In the Where Condition editor, then do one of the following:

      To learn about adding date ranges, see Set Date Ranges.

    Notes:

    • If you reverse engineer a query that contains a Where condition from the Editor or edit it in the Query tab of the Query Builder, it displays in a Global Where clause bubble on the Diagram pane rather than the Where Condition field below the Diagram pane. You can double-click the bubble to edit the clause, or can click to edit it.
    • You can also use the Where Condition to add the same column twice to the query.

    Or

    Select the operators and expressions to add to the OR condition. See Add Or Conditions for more information.

    Group By

    Select the column you want to add the Group By clause to and click +.

    You can add a new Group By clause to any remaining columns to group them in sequence. Any remaining columns that do not have a Group By clause must include an aggregate function.

    Tip: You can add a Group By clause to all selected columns in each table/view, by right-clicking the Diagram pane and selecting Add Group By.

    Having Condition

    Select the operators, aggregate, and expressions to include in the Having condition. This option is disabled unless you have set a Group By clause. See Set Having Conditions for more information.

    Note: If you reverse engineer a query that contains a Having condition from the Editor or edit it in the Query tab of the Query Builder, it displays in a Global Having clause bubble (click here to see an example) on the Diagram pane rather than the Having Condition field below the Diagram pane. You can double-click the Global Having clause to edit it.

    Sort

    Select an option to add this column to the Order By clause and specify a sort direction.

    Visible

    Select this checkbox to return this column in query results. This is useful if you need to include a column in the selection criteria, but do not need to display it in the query results.

    Field Alias

    Enter a name to use as an alias for the column name in the query results. This is useful if you have an ID or vague column name and want to easily identify that column in the query results.

    Table Alias

    Enter a name to use as an alias for the table name in the query results. For example, if there are multiple employee tables that you need to join for the query, you can rename the tables to permanent, contract, etc., to easily identify them.  

    Note: If you selected All Columns for a table or created a column using the Edit Calculated Fields window, you cannot modify the table alias.

  2. Add a subquery, if needed. See Add Subqueries for more information.

  3. Click to execute the query.

  4. To save the query, right-click the Query Builder tab and select Save File. If you save the file as a Query Builder file (.tsm), Toad saves the current connection with the file and you cannot change it by switching connections. To view the connection associated with the file and preview the script, place the cursor over the Query Builder tab (see the following image).



    You can also save a query and any results sets in a Toad editor file (.tef). This file format is useful for building scripts that have large result sets that you do not want to continue executing or for saving results when you have not finished building a script.  

Tips:

 

Related Topics

Reverse Engineer Queries 

Join Columns 

Add Union Operators

View Generated Queries 

Add Subqueries