'OUTER JOIN' Class Notes

Spring 2018

This join of parcels and fires drops out any parcel that didn't have a fire:

select p.parcelid, add1, add2, count(fdate) fires
        from parcels p, fires f
       where p.parcelid = f.parcelid
    group by p.parcelid, add1, add2;

This set of queries will save the results of the previous join in a table and then add in one row for every parcel in the parcel table that didn't have a fire:

create table t1holdf as
select p.parcelid, add1, add2, count(fdate) fires
    from parcels p, fires f
   where p.parcelid = f.parcelid
   group by p.parcelid, add1, add2;
insert into t1holdf
select parcelid, add1, add2, 0
    from parcels
   where parcelid NOT IN (select parcelid from fires);

Now, here's an 'outer join' SQL statement that redoes the original query in a way that adds
in the rows for parcels without fires (just like the two-step create/insert statements above). (We've added an additional column--the sum of the estimated fire losses--so you see how NULLs are handled).

      select p.parcelid, add1, add2, count(fdate) fires, sum(estloss)
        from parcels p, fires f
       where p.parcelid = f.parcelid (+)
    group by p.parcelid, add1, add2;

Back to the 11.521 Home Page
Back to the CRL Home Page
Back to the MIT Home Page

For more information about this page, please contact the 11.521 Staff Mailing List <11.521staff@MIT.EDU>.

Written by Joseph Ferreira, Jr. on 9 Oct. 1997.
Converted to HTML by Thomas H. Grayson on 1 Feb. 2000.
Last modified: 4 February 2018 [rb]

Massachusetts Institute of Technology Department of Urban Studies and Planning

'OUTER JOIN' Class Notes

Spring 2018

Massachusetts Institute of Technology
Department of Urban Studies and Planning