Creating a Clause Using the SQL Statement Tree

From RapidSQL
Jump to: navigation, search

Go Up to Using Query Builder

Query Builder lets you build more detailed WHERE, ORDER BY, GROUP BY, and HAVING clauses using the SQL Statement Tree. Query Builder lets you add clauses to SELECT, UPDATE, DELETE, and CREATE VIEW statements.

Note: Query Builder does not support clauses for INSERT statements.

For more information, see the following topics:

Creating a WHERE Clause

Query Builder lets you create a WHERE clause from the SQL Statement Tree which automatically displays in your query.

Note: Any additional WHERE clauses are displayed as HAVING clauses.

The table below describes the options and functionality on the Where dialog box.

Option Description

Operand (Left)

Lets you click the target column for the first part of your WHERE clause. NOTE: Query Builder lists every column in all tables in the SQL Diagram in the Operand lists.

Operator

Lets you select the target operator.

Operand (Right)

Lets you click the target column for the second part of your WHERE clause. Query Builder automatically writes the query language in the Statement option box.

Note: Query Builder does not display clause phrases created from the SQL Statement Tree in the SQL Diagram Pane.

Creating a WHERE Clause

To Create a WHERE clause, do the following:

  1. Click the WHERE node, and then on the Query Builder tool bar, click New.
OR
Right-click the WHERE node, and then click New.

Deleting a WHERE Clause

To delete a WHERE clause, do the following:

  1. Expand the AND node, and then on the Query Builder tool bar, click Delete.
OR
Expand the AND node, right-click target column and then click Delete.
Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Creating an AND Clause in a WHERE Clause

Query Builder lets you add an AND clause from the SQL Statement Tree which automatically displays in your query.

The table below describes the options and functionality on the Where dialog box.

Option Description

Operand (Left)

Lets you click the target column for the first part of your WHERE clause.

Operator

Lets you select the target operator.

Operand (Right)

Lets you click the target column for the second part of your WHERE clause. Query Builder automatically writes the query language in the Statement option box.

New Button

Click to clear your selections but remain in the Where dialog box. Query Builder adds another AND clause to your query.

To open the Where dialog box, do the following:

  1. Click the AND node, and then on the Query Builder tool bar, click New.
OR
Expand the WHERE node, right-click the AND node, and then click New.

Deleting an AND Clause

To delete an AND clause, do the following:

  1. Expand the AND node, click target column, and then on the Query Builder tool bar, click Delete.
OR
Expand the AND node, click target column, and then on the keyboard press DELETE.
OR
Expand the AND node, right-click the target column, and then click Delete.
Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Inserting an AND or OR Clause

Query Builder lets you insert an AND or an OR WHERE clause from the SQL Statement Tree which automatically displays in your query. Query Builder lets you insert AND or OR clauses at any appropriate point in the SQL Statement Tree.

The table below describes the options and functionality on the Where dialog box.

Option Description

Operand (Left)

Lets you click the target column for the first part of your WHERE clause.

Operator

Lets you select the target operator.

Operand (Right)

Lets you click the target column for the second part of your WHERE clause. Query Builder automatically writes the query language in the Statement option box.

New Button

Click to clear your selections but remain in the Where dialog box. Query Builder adds another AND clause to your query.

To insert an AND or OR Clause, do the following:

  1. On the SQL Statement Tree, expand the WHERE node, right-click the target AND node, then click Insert, and then click And or Or.

Deleting an OR Clause

To delete an OR clause, do the following:

  1. Expand the OR node, and then on the Query Builder tool bar, click Delete.
OR
Expand the OR node, right-click the target column and then click Delete.
Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Creating an ORDER BY Clause

Query Builder lets you create an ORDER BY clause from the SQL Statement Tree which automatically displays in your query.

The table below describes the Order By Columns dialog box.

Option Description

Available Columns

Select target column(s) and click the right arrow. Query Builder moves target column from the Available Columns list to the Order By Columns list. NOTE: Query Builder sorts query results based on the order that columns are placed in the ORDER BY clause.

Order

Lets you select the target sort order. ASC - Ascending DESC - Descending Query Builder displays the SQL language in the Order By Statement box.

To open the Order By Columns dialog box, do the following:

  1. On the SQL Statement Tree, click the ORDER BY node, and then on the Query Builder tool bar, click Properties.
OR
On the SQL Statement Tree, right-click the ORDER BY node, and then click Properties.

Changing the Sort Order in an ORDER BY Clause

To quickly change the sort order of a column in a query, do the following:

  1. On the SQL Statement Tree, expand the ORDER BY node, and then double-click the target column.
    OR
    On the SQL Statement Tree, expand the ORDER BY node, then right-click the target column, and then click Properties.
    Query Builder opens the Order dialog box.
  2. Click the target sort order, and then click OK.
    Query Builder appends the Order By clause for target column with the appropriate sort order in the SQL Statement Pane.

Deleting an ORDER BY Clause

To delete an ORDER BY clause, do the following:

  1. Expand the ORDER BY node, and then on the Query Builder tool bar, click Delete.
    OR
    Expand the ORDER BY node, right-click the target column, and then click Delete.
    Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Creating a GROUP BY Clause

The table below describes the options and functionality on the Group By Columns dialog box.

Option Description

Selected Columns

Select target column(s) and click the right arrow. Or click the Select All button. Query Builder moves target column from the Selected Columns list to the Group By Columns list. NOTE: Query Builder sorts query results based on the order that columns are placed in the ORDER BY clause.

Clear All Button

Click to move target column from the Group By Columns list to the Selected Columns list. Query Builder displays the SQL language in the Group By Statement window.

Creating a GROUP BY Clause

To create a GROUP BY clause from the SQL Statement Tree which automatically displays in your query, do the following:

  1. On the SQL Statement Tree, double-click the GROUP BY node.
    OR
    On the SQL Statement Tree, right-click the GROUP BY node, and then click New.
    Query Builder adds all the selected columns in your table(s) to the GROUP BY node in the SQL Statement Tree, and to the appropriate location in the SQL Statement Pane.
  2. On the GROUP BY node, double-click any column.
    OR
    On the GROUP BY node, click any column, then on the Query Builder menu, click New.
    OR
    On the GROUP BY node, right-click any column, then click Properties.

Deleting a GROUP BY Clause

To delete a GROUP BY clause, do the following:

  1. On the SQL Statement Tree expand the GROUP BY node, and then on the Query Builder tool bar, click Delete.
    OR
    On the SQL Statement Tree Expand the GROUP BY node, right-click the target column, and then click Delete.
    Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Creating a HAVING Clause

A HAVING clause is a type of WHERE clause. It filters additional information from your tables. Query Builder lets you create a HAVING clause from the SQL Statement Tree which automatically displays in your query. Query Builder lists every column in all tables in the SQL Diagram in the Operand lists. Query Builder displays the datatype of a column in the operand boxes.

The table below describes the options and functionality on the Having dialog box.

Option Description

Operand (Left)

Lets you click the target column for the first part of your HAVING clause.

Operator

Lets you select the target operator.

Operand (Right)

Lets you click the target column for the second part of your HAVING clause. Query Builder automatically writes the query language in the Statement option box.

New Button

Click to clear your selections but remain in the Having dialog box. Query Builder adds another AND clause to your query.

Note: Query Builder does not display clause phrases created from the SQL Statement Tree in the SQL Diagram Pane.

To create a HAVING clause, do the following:

  1. On the SQL Statement Tree, expand the HAVING node, and then expand the And node. If there is not a join listed on the And node, double-click And. If there is a join listed, use the shortcut option below.
    OR
    On the SQL Statement Tree, right-click the HAVING node, and then click New.

Deleting a HAVING Clause

To delete a HAVING clause, do the following:

  1. On the SQL Statement Tree expand the HAVING node, and then on the Query Builder tool bar, click Delete.
    OR
    On the SQL Statement Tree expand the HAVING node, right-click the target column, and then click Delete.
    Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Changing Tables and Columns Location in the SQL Statement Tree

Query Builder lets you move tables and columns on the SQL Statement Tree by dragging them to new locations. You can move columns from the AND and OR nodes to an AND or OR node on the WHERE and HAVING clause nodes. Query Builder changes the query in the SQL Statement Pane to match each move. Query Builder moves tables or columns you are dragging below target table or column.

To move a table or column in the SQL Statement Tree, do the following:

  1. Expand target node, then drag the target table or column to a new location.
    Query Builder makes the appropriate change in the query in the SQL Statement Pane.
    Note: Query Builder lets you select multiple tables or columns.
  2. To move a table or column to the bottom of a node, drag it to the target node.
    Query Builder displays the target table or column at the bottom of target node.


Topics