VERIFIED SOLUTION i

Query syntax for outer joins in MapInfo Pro

Products affected: MapInfo Pro™
An SQL outer join refers to the process of joining data from a larger table and a smaller table where users would like the result to be all the records in the larger table joined to whatever records matched from the smaller table.

A problem arises if this is done as a standard join. A standard join in SQL Select will yield a table of only those records that matched.
Users would like to have empty fields where there is no match.
For example, suppose users have a table containing a listing of all of the apartments in a building.
There is also a table containing records for each of the tenants.
The tables each have a field with the apartment number in it. Your task is to generate a table of all of the apartments and the tenants occupying the apartments.
Some of the apartments will be vacant and have no match in the tenant field.

The first step is to create a subset of the data where one can successfully match the apartment to the tenant.

 
1. On the Query menu, click SQL Select. Set up the following SQL query:
a. Select Columns: APARTMNT.Address, APARTMNT.AptNumber, TENANTS.Name
b. from Tables: APARTMNT, TENANTS
c. where Condition: APARTMNT.AptNumber TENANTS.AptNumber
d. into Table Named: Selection

2. Click OK. The selection appears as a query browser. Save this query to a base table.

3. On the File menu, click Save Copy As. The Save Copy As dialog box displays. Choose the appropriate directory for the file and name it RESULT.TAB. Click Save.

4. On the File menu, click Open Table and open the RESULT table. This table includes all of the records from both tables where there was a match.

5. Select the records from the APARTMNTS table that had no match in the tenants table. On the Query menu, click SQL Select and set up the following SQL query:
a. Select Columns: *
b. from Tables: APARTMNT
c. where Condition: APARTMNT.AptNumber Not In
(SELECT AptNumber from RESULT)
d. into Table Named: Selection

The resulting query table is a list of all of the apartments that are not in the RESULT table. To include these records in your RESULT table, one must append them.

6. On the Table menu, click Append Rows to Table. Append the last query table to the RESULT table. This appends the list of vacant apartments to the list of occupied apartments.
UPDATED:  June 4, 2018