Join the objects of one table with the objects of another, based on the given 
        matching attributes in the named arguments.  The attrlist specifies the attributes to 
        be copied from the source tables - if omitted, all attributes will be copied.  Entries 
        in the attrlist may be single attribute names, or if there are duplicate names in both
        tables, then a C{(table,attributename)} tuple can be given to disambiguate which 
        attribute is desired. A C{(table,attributename,alias)} tuple can also be passed, to 
        rename an attribute from a source table.
        
        This method may be called directly, or can be constructed using the L{join_on} method and
        the '+' operator.  Using this syntax, the join is specified using C{table.join_on("xyz")}
        to create a JoinTerm containing both table and joining attribute.  Multiple JoinTerm
        or tables can be added to construct a compound join expression.  When complete, the 
        join expression gets executed by calling the resulting join definition, 
        using C{join_expression([attrlist])}.
        
        @param other: other table to join to
        @param attrlist: list of attributes to be copied to the new joined table; if 
            none provided, all attributes of both tables will be used (taken from the first 
            object in each table)
        @type attrlist: string, or list of strings or C{(table,attribute[,alias])} tuples
            (list may contain both strings and tuples)
        @param kwargs: attributes to join on, given as additional named arguments
            of the form C{table1attr="table2attr"}, or a dict mapping attribute names.
        @returns: a new Table containing the joined data as new DataObjects

def join(self, other, attrlist=None, auto_create_indexes=True, **kwargs):
        """
        Join the objects of one table with the objects of another, based on the given 
        matching attributes in the named arguments.  The attrlist specifies the attributes to 
        be copied from the source tables - if omitted, all attributes will be copied.  Entries 
        in the attrlist may be single attribute names, or if there are duplicate names in both
        tables, then a C{(table,attributename)} tuple can be given to disambiguate which 
        attribute is desired. A C{(table,attributename,alias)} tuple can also be passed, to 
        rename an attribute from a source table.
        
        This method may be called directly, or can be constructed using the L{join_on} method and
        the '+' operator.  Using this syntax, the join is specified using C{table.join_on("xyz")}
        to create a JoinTerm containing both table and joining attribute.  Multiple JoinTerm
        or tables can be added to construct a compound join expression.  When complete, the 
        join expression gets executed by calling the resulting join definition, 
        using C{join_expression([attrlist])}.
        
        @param other: other table to join to
        @param attrlist: list of attributes to be copied to the new joined table; if 
            none provided, all attributes of both tables will be used (taken from the first 
            object in each table)
        @type attrlist: string, or list of strings or C{(table,attribute[,alias])} tuples
            (list may contain both strings and tuples)
        @param kwargs: attributes to join on, given as additional named arguments
            of the form C{table1attr="table2attr"}, or a dict mapping attribute names.
        @returns: a new Table containing the joined data as new DataObjects
        """
        if not kwargs:
            raise TypeError("must specify at least one join attribute as a named argument")
        thiscol, othercol = next(iter(kwargs.items()))

        retname = ("(%s:%s^%s:%s)" % (self.table_name, thiscol, other.table_name, othercol))
        # make sure both tables contain records to join - if not, just return empty list
        if not (self.obs and other.obs):
            return Table(retname)
        
        if isinstance(attrlist, basestring):
            attrlist = re.split(r'[,\s]+', attrlist)
            
        # expand attrlist to full (table, name, alias) tuples
        thisnames = set(_object_attrnames(self.obs[0]))
        othernames = set(_object_attrnames(other.obs[0]))
        fullcols = []
        if attrlist is not None:
            for col in attrlist:
                if isinstance(col, tuple):
                    # assume col contains at least (table, colname), fill in alias if missing 
                    # to be same as colname
                    fullcols.append((col + (col[1],))[:3])
                else:
                    if col in thisnames:
                        fullcols.append((self, col, col))
                    elif col in othernames:
                        fullcols.append((other, col, col))
                    else:
                        raise ValueError("join attribute not found: " + col)
        else:
            fullcols = [(self, n, n) for n in thisnames]
            fullcols += [(other, n, n) for n in othernames]

        thiscols = list(filter(lambda o: o[0] is self, fullcols))
        othercols = list(filter(lambda o: o[0] is other, fullcols))

        if auto_create_indexes:
            if thiscol not in self._indexes:
                self.create_index(thiscol)
            if othercol not in other._indexes:
                other.create_index(othercol)

        if thiscol in self._indexes:
            thiscolindex = self._indexes[thiscol]
        else:
            raise ValueError("indexed attribute required for join: "+thiscol)
        if othercol in other._indexes:
            othercolindex = other._indexes[othercol]
        else:
            raise ValueError("indexed attribute required for join: "+othercol)

        # use table with fewer keys to drive join
        if len(thiscolindex) < len(othercolindex):
            shortindex, longindex = (thiscolindex, othercolindex)
            swap = False
        else:
            shortindex, longindex = (othercolindex, thiscolindex)
            swap = True
            
        # find matching rows
        matchingrows = list((longindex[key], rows) if swap else (rows, longindex[key])
                            for key, rows in shortindex.items())

        joinrows = []
        for thisrows, otherrows in matchingrows:
            for trow, orow in product(thisrows, otherrows):
                retobj = DataObject()
                do_all(setattr(retobj, a, getattr(trow, c)) for _, c, a in thiscols)
                do_all(setattr(retobj, a, getattr(orow, c)) for _, c, a in othercols if not hasattr(retobj, a))
                joinrows.append(retobj)

        ret = Table(retname)
        for tbl, collist in zip([self, other], [thiscols, othercols]):
            for _, c, a in collist:
                if c in tbl._indexes:
                    if a not in ret._indexes:
                        ret.create_index(a)  # no unique indexes in join results
        ret.insert_many(joinrows)
        return ret