Queries can incorporate fields from different tables. It is the relationship you set up between the data in these tables that determines how the data appears in the result set.

Users can set up these relationships between tables using SQL Joins like Inner Join, Left Join and Right Join in the Visual Query Designer.

1. Inner Join (simple join) - Inner Join matches rows from Table1 with rows in Table2, and allows the user to retrieve records that show a relationship in both the tables. Inner join produces a set of data that matches both Table1 and Table2.
   
   Syntax for the SQL Inner Join:
   SELECT columns
   FROM table1
   INNER JOIN table2
   ON table1.column = table2.column;
   
   Visual Illustration
   
   SQL Inner Join returns the records where table1 and table2 intersect.
   
2. Left Outer Join (left join) - Left Outer Join allows users to select rows that match from both the left and right tables, plus all the rows from the left table (table 1). This means only those rows from table2 that intersect with table1 appear in the result set.
   
   Syntax for SQL Left Outer Join:
   SELECT columns
   FROM table1
   LEFT [OUTER] JOIN table2
   ON table1.column = table2.column;
   
   Visual Illustration
   
   SQL Left Outer Join returns the records from table1 and only those records from table2 that intersect with table1.
   
3. Right Outer Join (right join) - Right outer join allows users to select rows that match from the both the left and right tables, plus all the rows from right table (table 2). This means that only those rows from table 1 that intersect with table 2. appear in the result set
   
   Syntax for SQL Right Outer Join:
   SELECT columns
   FROM table1
   RIGHT [OUTER] JOIN table2
   ON table1.column = table2.column;
   
   Visual Illustration
   
   SQL Right Outer Join returns the records from table 2 and only those records from table 1 that intersect with table2.

Tables relations dialog

The Tables relations dialog allows users to set up a relationship between two different tables with at least one common field.

Complete the following steps to access the Tables relations dialog:

1. In the Visual Query Designer, drag and drop a field or fields from a table in the Database view to the Selection Fields panel.
2. Add another field from a different table in the Database tab to the Selected Fields panel. Make sure that at least one field between these two tables' matches i.e. the second table contains a foreign key.
3. When the field in step 2 is added, the Tables relations dialog automatically pops up on the screen.

Once the relationship has been set up between tables, you may also access the Tables relations dialog from the Relations button in the Tables and Relationships panel.

Option	Description
Join Type	Enables selection of an appropriate Join type out of Inner Join, Left Outer Join and Right Outer Join. Example: Inner Join tab is highlighted in the image above.
<Table Name> Field	Displays the name of the field that is common between tables i.e. the foreign key name in the second table. Example: "Products" Field contains the 'Category ID' field in the image above.
Related Table	Displays the name of the table to which the relationship has been set up. Example: 'Categories' table is listed in the image above.
Related Table's Field	Displays the name of the field from the table to which the relationship has been set up. Example: 'Category ID' is the field from the Categories tables listed in the image above.
Delete	Icon adjacent to the Related Table's Field to delete the currently added relation.
Add Relation	Button that allows users to add another relationship to the table.
Cancel	Closes the Tables relations window.
OK	Saves the relationship between tables as a SQL query in the SQL tab.

Go to Top