CODEWITHSUNDEEP
sqlsql-serverjoinsql-server-2012outer-join
Mike
Mike

Reputation: 1069

SQL: Using outer join in SQL Server 2012

I'm trying to learn how to use the OUTER JOIN function in SQL Server, so that the select query returns all values from two tables, even the unmatched ones.

Here are the two sample tables I'm using (Boys and Girls): 

Boys:

Name    City
Andrew  Cape Town
Mike    Paphos
Simon   Amsterdam

Girls:

Name    City
Georgia Paphos
Megan   London
Eva     Amsterdam
Stephi  Cape Town

Here is the query I've written:

SELECT boys.name, boys.city, girls.name, girls.city
    FROM boys, girls
    RIGHT OUTER JOIN boys as men ON boys.city=girls.city;

I'm trying to return a table as follows:

boys.Name     boys.City        girls.name        girls.city    
Andrew        Cape Town        Stephi            Cape Town  
Mike          Paphos           Georgia           Paphos  
Simon         Amsterdam        Eva               Amsterdam  
NULL          NULL             Megan             London

A normal inner join ignores the last line because it doesn't have a matching boy city.

Could anyone help me with this?

Thanks!

Mike

Upvotes: 2

Views: 577

Answers (2)

burckhartkenton9
burckhartkenton9

Reputation: 1

You could use a FULL OUTER JOIN on the two tables.

You can find more details here.

For example:

SELECT boys.name, boys.city, girls.name, girls.city
    FROM girls
    FULL OUTER JOIN boys ON boys.city=girls.city;

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

You're looking for a FULL OUTER JOIN that goes both ways instead of a RIGHT OUTER JOIN;

SELECT boys.name boys_name, boys.city boys_city,
       girls.name girls_name, girls.city girls_city
FROM boys
FULL OUTER JOIN girls 
  ON boys.city=girls.city

An SQLfiddle to test with.

Your existing query joins boys twice, once with the old join syntax and once with a right join. Using full outer join, you only have to include each table once.

Upvotes: 4

Related Questions