|
Home >> FAQs/Tutorials >> SQL Server FAQ
SQL Server FAQ - "FULL OUTER JOIN ... ON" - Writing Queries with Full Outer Joins
By: FYIcenter.com
(Continued from previous topic...)
How To Write a Query with a Full Outer Join?
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.
(Continued on next topic...)
- How To Join Two Tables in a Single Query?
- How To Write a Query with an Inner Join?
- How To Define and Use Table Alias Names?
- How To Write a Query with a Left Outer Join?
- How To Write a Query with a Right Outer Join?
- How To Write a Query with a Full Outer Join?
- How To Write an Inner Join with the WHERE Clause?
- How To Name Query Output Columns?
- What Is a Subquery in a SELECT Query Statement?
- How To Use Subqueries with the IN Operators?
- How To Use Subqueries with the EXISTS Operators?
- How To Use Subqueries in the FROM Clause?
- How To Count Groups Returned with the GROUP BY Clause?
- How To Return the Top 5 Rows from a SELECT Query?
- How To Return the Second 5 Rows?
- How To Use UNION to Merge Outputs from Two Queries Together?
- How To Use ORDER BY with UNION Operators
|