How To Write a Query with a Full Outer Join in MS SQL Server?

Submitted by: Administrator
If you want to query from two tables with a full outer join, you can use the FULL OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a full outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:

SELECT l.id, l.url, r.comment FROM ggl_links l
FULL OUTER JOIN ggl_rates r ON l.id = r.id
GO
<pre>
id url comment
101 www.globalguideline.com The best
102 www.globalguideline.com/html Well done
103 www.globalguideline.com/seo Thumbs up
104 www.google.com NULL
105 www.yahoo.com NULL
106 www.php.net NULL
107 www.mysql.com NULL
NULL NULL Number 1
NULL NULL Not bad
NULL NULL Good job
NULL NULL Nice tool
</pre>
As you can see, an full outer join returns 3 groups of rows:

* The rows from both tables that satisfy the join condition.
* The rows from the first (left) table that do not satisfy the join condition.
* The rows from the second (right) table that do not satisfy the join condition.
Submitted by: Administrator

Read Online MS SQL Server Job Interview Questions And Answers