The SQLyog Query Builder

SQLyog provides you with a powerful GUI (graphical) Query Builder. The Query Builder is a 'special TAB in the SQL editor area of the program. When the program opens it will display one such tab. You can add more from the 'file' and the 'powertools' menu.

The Query Builder can be selected by clicking on the "+" icon in the query editor line to the right of all open tabs and select the option “New Query Builder”,

or from the ‘File’ menu,

The Query Builder TAB is divided into 3 sections:

  • a 'canvas' (a drawing area)
  • a GRID area
  • a (read only) editor area

You can move the 'divider' between those three areas to fit the needs of the individual queries that you are building.

Also note that there is one setting in 'Preferences' that affects the behavior of the Query Builder: you can choose to use `backquotes` to enclose table and column names or not. You need to use `backquotes` in some special cases: for instance if the name of a table or column is a word that has a special meaning in MySQL (a 'reserved word'), like for instance the words 'select', 'table', 'begin' etc. Also if one or more names starts with a number or contains special characters you may need depending on the MySQL version. Using `backquotes` is SAFE, but some users think that they make queries hard to read.

To start building a query use the mouse to drag the tables that shall be used for this particular query from the Object Browser into the canvas of the Query Builder. You can arrange them and resize them as you want. If some lines occur between two of the tables it is because there are Foreign Keys defined between those tables. The line indicates a SQL JOIN. It is reasonable to think the columns 'related' by defining a Foreign Key should be JOINed. So, SQLyog will propose you that. You can right click on the line and define the properties of the JOIN or delete it if you want.

To define JOINs that are not defined 'implicit' in the database schema itself as Foreign Keys, you just draw a line with the mouse between the two columns that shall be JOINed. So JOIN is possible too with table types that do not support Foreign Keys.

Note that (from version 6.5) also VIEWs are supported by the Query Builder. You operate them completely identically. In the following the term 'table' refers to both tables and VIEWs!

Table Alias: When table is added to the canvas more than once, a table alias is generated for second and following instances and the alias is used by Query Builder in queries. Also, you may define a table alias yourself for any table by double-clicking the title bar of the table symbol ('box'). The table alias will display in the title bar with the table name in <brackets>.

Once the tables are dragged into the canvas and the JOINs properly set up start selecting the columns that shall be included with the RESULT of the query. There are three ways to do this:

  • Selecting the table and the column in the GRID by using the drop-downs for 'table' and 'column'.
  • Dragging a column from the canvas into the GRID. This (data) column will be added before the (GRID) column where you pull it.
  • Double-clicking a column of a table in the canvas. The column will be added at the end/right of the GRID.

Now the Query Tab will look like the image below. Note that a query is already generated and displayed in the Query Builder editor area. This is a simply query only doing SELECTs on the columns that you have chosen to display in the GRID JOINing the columns with the JOIN type that you selected.

You can copy the generated query from the Query Builder editor area to a 'common' or 'normal' Query Tab from where you can execute and do anything else you would do with a query that you had written yourself. You can also copy to the clipboard. The icons just below the GRID are for those copy operations.

In the image above you also see that changing a JOIN type or deleting line representing a JOIN from the canvas is done by right-clicking on the line itself, and selecting from the context menu.

Note that LEFT and RIGHT JOINs have a different meaning depending on the direction of the JOIN. LEFT JOINing table a on table b returns the same as RIGHT JOINing table b on table a (and vice versa). With INNER JOINs the result is the same. The JOIN direction is decided by the program from recording in which direction the line in the canvas was drawn. If you are not very familiar with JOINs it will probably be a good idea always to use a fixed procedure: for instance consider always having 'child' tables to the left of 'parent' tables and always draw in the canvas from left to right. Lines drawn by the program itself (due to the existence of Foreign Keys) will always be considered as being drawn from 'child' to 'parent'.

Also note that the SQLyog Query Builder updates the displayed query 'in real time' - that is whenever a column is added or removed from the GRID or any kind of parameter is entered in the GRID, the query is updated at the very moment that the cursor leaves that cell of the GRID where you added or changed something.

Finally, note that you can INSERT or DELETE a complete column in the GRID by right-clicking on the small rectangle on the top of each column.

Note: This feature is available in SQLyog Enterprise and Ultimate.