Performing Outer Joins - MapInfo_Pro - 2023

An SQL outer join refers to the process of joining data from a larger table and a smaller table where you 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. You would like to have empty fields where there is no match. For example, suppose you 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 you can successfully match the apartment to the tenant.

1. On the MAP tab, click SQL Select. Set up the following SQL query:
   • Select Columns: APARTMNT.Address, APARTMNT.AptNumber, TENANTS.Name
   • from Tables: APARTMNT, TENANTS
   • where Condition: APARTMNT.AptNumber TENANTS.AptNumber
   • into Table Named: Selection
2. Click OK. Your selection appears as a query browser. Save this query to a base table.
3. On the HOME tab, click Save Copy As. The Save Copy As dialog box displays. Choose the appropriate directory for your file and name it RESULT.TAB. Click Save.
4. On the HOME tab, in the File group, from the Open list, click 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 MAP tab, click SQL Select and set up the following SQL query:
   • Select Columns: *
   • from Tables: APARTMNT
   • where Condition: APARTMNT.AptNumber Not In
   • 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, you must append them.

6. On the TABLE tab, click Append Rows. Append the last query table to the RESULT table. This appends the list of vacant apartments to the list of occupied apartments.