SQL Server: use left join to select a column from two tables as a single column

I am trying to build a select query that will essentially left join two tables and display one column from each table as a single column.

the table structure is similar to:

table a:

id, email

table b:

id, tablea_id, emai开发者_如何学运维l

I am trying to get a single column of email and email (with no dupes or nulls ideally).

ideal results would be:

one@one.com
two@two.com
three@three.com

and the email address that is returned could be from either a or b.

Maybe a union is what would work best, but I not able to figure out how to do a union on the second table based on the id of the first table.

When searching for a solution, perhaps my wording is bad, but I can't find any examples.

thanks for any help.

It seems that you want something on the lines of this:

SELECT email
FROM TableA
UNION
SELECT B.email
FROM TableA A
JOIN TableB B
ON A.id = B.TableA_id

If you simply want a unique list of email addresses from either table you can do:

Select email
From TableA
Union
Select email
From TableB

If you are looking for a unique list of email addresses from Table B and those from Table A that exist in Table B, then you can do:

Select TableA.email
From TableA
    Join TableB
        On TableB.TableA_id = TableA.id
Union
Select email
From TableB

If, per your comments, you need all rows from Table A and only rows from Table B where they exist in Table A:

Select email
From Table A
Union 
Select TableB.email
From TableB
    Join TableA
        On TableA.id = TableB.TableA_id

How about this:

SELECT DISTINCT a.email+ ' ' + b.email FROM tableA a LEFT JOIN tableB b on a.Id = b.tablea_Id WHERE b.email IS NOT NULL

This could help:

SELECT
    id, email
FROM
    a

UNION

(
    SELECT
        a.id AS id, b.email AS email
    FROM
        b
    INNER JOIN
        a
    ON
        a.id = b.tablea_id
) b