Try to check for Null value in the fields from name and address table It would indicate that there are no related records in that table.

SELECT id.*, NVL(nm.lname, ' ') lname,
	NVL(nm.fname, ' ') fname, NVL(nm.mname, ' ') mname,
	NVL(ad.city, ' ') city, NVL(ad.street, ' ') street...
	FROM id_table id 
		LEFT JOIN name_tbl nm 	ON id.id = nm.id
		LEFT JOIN address_tbl ad ON id.id = ad.id
	WHERE (nm.effective_date IS NULL OR TTOC(nm.effective_date, 1) =
		(SELECT MAX(TTOC(x.effective_date, 1)) FROM name_tbl x WHERE x.id = nm.id)) 
	AND (ad.effective_date IS NULL OR TTOC(ad.effective_date, 1) =
		(SELECT MAX(TTOC(y.effective_date, 1)) FROM address_tbl y WHERE y.id = ad.id))

>I have an ID table which links out to a name table and also links out to an address table. The name table contains all of the name changes of a customer through time using effective_dates. The address table does the same thing using effective_dates. In other words, we never delete or update any records in the name or address table; just add.
>
>This is a sample SQL:
>
>SELECT a.*, NVL(b.lname, ' ') lname,
>NVL(b.fname, ' ') fname, NVL(b.mname, ' ') mname,
>NVL(c.city, ' ') city, NVL(c.street, ' ') street...
>FROM id_table a LEFT JOIN name_tbl b
>ON a.id = b.id
>LEFT JOIN address_tbl c
>ON a.id = b.id
>WHERE TTOC(b.effective_date, 1) =
>(SELECT MAX(TTOC(x.effective_date, 1)) FROM name_tbl x WHERE x.id = b.id) AND
>TTOC(c.effective_date, 1) =
>(SELECT MAX(TTOC(y.effective_date, 1)) FROM address_tbl y WHERE y.id = c.id)
>
>This effectively works out to an inner join anyway because of the where clause. That means that if an id does not have a name or address applied to it, the record is ignored.
>
>That gets us to the union.
>
< snip >