When using an Outer Join, rows are not required to have matching values. The table order in the FROM clause specifies the left and right table. For a Left Outer Join, R:BASE uses each value unique to the left (first) table and completes it with nulls for the columns of the right (second) table when the linking columns do not match.
The following instructions will step through the process to recreate a Left Outer Join in the Query Builder.
To continue, launch R:BASE 11 and connect to the RRBYW20 sample database. The database is located in the following default installation directory: "C:\RBTI\RBG11\Samples\RRBYW20".
1.Start R:BASE
2.Connect to the RRBYW20 sample database, by selecting "Database" > "Connect", and navigating to the above default installation directory based upon your version.
3.Then, launch the Query Builder by selecting "Tools" > "Query by Example" from the main menu bar
In the Query Builder you will see a list of tables and views within a panel to the left that can be added to your query. The list contains all tables and views for the connected database.
4.Add the "Titles" table to the query by selecting the table, and then selecting the "Add Table/View" button.
The table should now be listed under "Tables/Views In Use".
5.Right click on the "Titles" table under "Tables/Views In Use", and choose "Join Properties..." from the speed menu.
The "Join Properties" dialog will be displayed.
6.From the "Join Type" radio button options, select "Left Outer"
Take note that the other options within the window will become enabled. In this window you will select the linking columns for the join, the second table for the join, and the join operation. As alias can also be assigned to the linking column.
7.From the "Left Column" panel, choose "T1.EmpTID" from the "Column Name:" drop down box. The "T1" alias may vary from your R:BASE screen.
8.From the "Right Table/Column" panel, choose "Employee" from the "Table Name:" drop down box.
9.From the "Right Table/Column" panel, enter "T2" into the "Alias:" field to assign the table alias for the join. If "T2" is already used for the "Titles" table, then use "T3" as the alias.
10.Again in the "Right Table/Column" panel, choose the "EmpTID" column from the "Column Name:" drop down box. The alias you have assigned will appear in from of the column name.
11.From the "Join Operation" radio button options, choose the equal character (=).
Your end result for the Join Properties should look like, or close to, the following:
12.Select the "OK" button.
In the Query Builder main window, you should see the SQL syntax displayed in the bottom of the page as:
SELECT *
FROM Titles T1 LEFT OUTER JOIN Employee T2 ON T1.EmpTID = T2.EmpID
The asterisk (*) in the command syntax represents all columns. The next steps will show how to specify certain columns to display.
13.Now, browse the query results by selecting "Query" > "Browse Query" from the main menu bar. The results should look like the following:
The results show the Employee table added to the Titles table. Where no employees match for an existing title, the row is empty, or null. In the above, there are no employees that have the "Director Corporate Sales" or "Manager Support & Services" titles.
If you wish to save the query, follow the below steps.
1.From the main menu bar, select "File" > "Save Query As View..."
2.In the dialog, enter "TitleEmpList" within the "View Name:" field.
3.In the dialog, enter "Title List with Employees" within the "View Comment:" field.
4.Select the "OK" button.
5.Close the Query Builder by selecting "File" > "Close" from the menu bar.
In the Database Explorer, the view will be displayed with the name, comment, and number of columns.
