MySQL - SELECT from multiple tables by unique attribute, return one row with all values

Question

I have the following three tables:

t1:
id | name
---------
1 | joe
2 | jon
t2:
id | street 
--------------
1 | "8th St." 
t3:
id | postcode
--------------
1 | "OC24 8BB"

How, using MySQL SELECT, can I specify a value for the common unique attribute 'id', and receive a single row combining the values from the three tables, with NULL values if none were found?

For example, if id = 1, I would expect the result:

id | name | street | postcode
----------------------------------
1 | joe | "8th St." | "OC24 8BB"

Whereas if id = 2, the following would be returned:

id | name | street | postcode
------------------------------
2 | jon | NULL | NULL

My current best attempt at the select is the following:

 SELECT A.id, A.name, B.street FROM t1 as A LEFT JOIN t2 AS B
 WHERE A.id = 1
 UNION ALL
 SELECT A.id, B.postcode FROM t1 as A LEFT JOIN t2 as B
 WHERE A.id = 1;

However this does not return the null values I require, I feel there is a simple solution however I have been looking for a while now and not found anything that has worked for me. Thank you if you can help shine some light on this problem.

Answer 1

You do not need UNION in this case, simple LEFT JOIN

SELECT 
 a.id, 
 b.street, 
 c.postcode
FROM t1 a
LEFT JOIN t2 b ON a.id=b.id
LEFT JOIN t3 c ON a.id=c.id

should work