/* Combining JOIN with UNION */

SELECT student_id AS id, fname, lname, street1, city, state, country, 'student' as type
FROM  student  JOIN  address ON student_id = stu_id

UNION

SELECT instructor_id, fname, lname, street1, city, state, country, 'instructor' 
FROM  instructor  JOIN  address ON instructor_id = inst_id
ORDER BY lname;									/* Sort by lname */




/* Try it with LEFT JOIN */

SELECT student_id AS id, fname, lname, street1, city, state, country, 'student' as type
FROM  student  LEFT JOIN  address ON student_id = stu_id

UNION

SELECT instructor_id, fname, lname, street1, city, state, country, 'instructor' 
FROM  instructor  LEFT JOIN  address ON instructor_id = inst_id
ORDER BY lname;