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

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: fyi_links and fyi_rates. The join condition is that the id in the fyi_links table equals to the id in the fyi_rates table:

SELECT l.id, l.url, r.comment FROM fyi_links l 
   FULL OUTER JOIN fyi_rates r ON l.id = r.id
GO
id      url                     comment
101     dev.fyicenter.com       The best
102     dba.fyicenter.com       Well done
103     sqa.fyicenter.com       Thumbs up
104     www.mysql.com           NULL
105     www.oracle.com          NULL
106     www.php.net             NULL
107     www.winrunner.com       NULL
NULL    NULL                    Number 1
NULL    NULL                    Not bad
NULL    NULL                    Good job
NULL    NULL                    Nice tool

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.

⇒ Writing Inner Joins with the WHERE Clause in SQL Server

⇐ "RIGHT OUTER JOIN ... ON" - Writing Queries with Right Outer Joins in SQL Server

⇑ Using SELECT Statements with Joins and Subqueries in SQL Server

⇑⇑ SQL Server Database Tutorials