0

I need run a query that is like an left/right outer join. In other words I need all rows from both the left and right tables. But I don't need a cartesian product (cross join). I need to match on, in my case, email address. So given that, I have to output all rows from the left table, join the right table on email address, but all rows that do not match from either the left or right table need to be output as well with nulls for the fields from the opposite table. Sort of like a = join if there were such a thing, or left-righ outer join.

As for what I've tried: Google Searches. But didn't find anything. Cross apply might work, but I cannot wrap my brain around how that is any different from a join.

Example theoretical left-right join:

    select users.*, contacts.*
    from users
    left-right join contacts on users.emailAddress = contacts.emailAddress

So if users contains:

    ----------------------------------
    |emailAddress        | firstName |
    ----------------------------------
    |k@company.com       | ken       |
    |b@enterprise.com    | bill      |
    |j@establishment.com | joe       |
    ----------------------------------

And contacts contains:

    --------------------------------
    |emailAddress       | optedOut |
    --------------------------------
    |z@bigcompany.com   |  0       |
    |b@enterprise.com   |  1       |
    |h@smallcompany.com |  1       |
    --------------------------------

The output should look like:

    ------------------------------------------------------------------
    |emailAddress        | firstName |emailAddress        | optedOut |
    ------------------------------------------------------------------
    |k@company.com       | ken       | NULL               | NULL     |
    |b@enterprise.com    | bill      | b@enterprise.com   | 1        |
    |j@establishment.com | joe       | NULL               | NULL     |
    |NULL                | NULL      | z@bigcompany.com   | 0        |
    |NULL                | NULL      | h@smallcompany.com | 1        |
    ------------------------------------------------------------------
Ken Hadden
  • 178
  • 12
  • @Tab Alleman I couldn't for the life of me find that answer. I just didn't know what that type of join was call so I wasn't searching correctly. I'd like to mark The Impaler's answer as correct since he figured it out, despite my horrible description. – Ken Hadden Jun 12 '19 at 18:17
  • Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS In this question you don't actually ever clearly state what you want. So searching is likely to be unfruitful. – philipxy Jun 12 '19 at 19:38
  • @philipxy I did. I just couldn't figure out how to phrase it in a way that got results. – Ken Hadden Jun 13 '19 at 20:02

1 Answers1

3

It's called a "full outer join". Your query should look like:

select users.*, contacts.*
from users
full outer join contacts on users.emailAddress = contacts.emailAddress
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Thanks @The Impaler. I've been using SQL Server for a very long time and just never had the need for this type of join. I hadn't even thought of looking at msdn. Which would have clued me in. – Ken Hadden Jun 12 '19 at 18:22