How To Write a Query with a Full Outer Join in MS SQL Server?
Submitted by: AdministratorIf 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
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
Top MS SQL Server Questions
☺ | How To Start SQL Server Browser Service? |
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | How To Test ODBC DSN Connection Settings? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Create a Testing Table with Test Data in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |