From the SQL 2000 Books Online:
<Excerpt href="acdata.chm::/ac_8_qd_09_0zqr.htm">
Inner joins return rows only when there is at least one row from both tables
that matches the join condition. Inner joins eliminate the rows that do not
match with a row from the other table. Outer joins, however, return all rows
from at least one of the tables or views mentioned in the FROM clause, as
long as those rows meet any WHERE or HAVING search conditions. All rows are
retrieved from the left table referenced with a left outer join, and all
rows from the right table referenced in a right outer join. All rows from
both tables are returned in a full outer join
Microsoft® SQL ServerT 2000 uses these SQL-92 keywords for outer joins
specified in a FROM clause:
LEFT OUTER JOIN or LEFT JOIN
RIGHT OUTER JOIN or RIGHT JOIN
FULL OUTER JOIN or FULL JOIN
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax
for specifying outer joins based on using the *= and =* operators in the
WHERE clause. The SQL-92 syntax is recommended because it is not subject to
the ambiguity that sometimes results from the legacy Transact-SQL outer
joins.
</Excerpt>
Here's some examples:
SELECT *
FROM Table1
JOIN Table2 ON Col1 = Col2
Col1 Col2
----------- -----------
3 3
SELECT *
FROM Table1
LEFT JOIN Table2 ON Col1 = Col2
Col1 Col2
----------- -----------
1 NULL
3 3
5 NULL
SELECT *
FROM Table1
RIGHT JOIN Table2 ON Col1 = Col2
Col1 Col2
----------- -----------
NULL 2
3 3
NULL 4
SELECT *
FROM Table1
FULL JOIN Table2 ON Col1 = Col2
Col1 Col2
----------- -----------
NULL 2
3 3
NULL 4
5 NULL
1 NULL
SELECT *
FROM Table1
CROSS JOIN Table2
Col1 Col2
----------- -----------
1 2
3 2
5 2
1 3
3 3
5 3
1 4
3 4
5 4
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andrey" <le*******@yahoo.com> wrote in message
news:iGo6d.128183$MQ5.77602@attbi_s52...
Hugo Kornelis wrote: On Tue, 28 Sep 2004 02:33:13 GMT, Andrey wrote:
knock-knock!
What is OUTER for here?
(snip)
And i'm not sure you can use OUTER & LEFT together - never headr of that
and found just an oracle example, nothing in t-sql.
Hi Andrey,
The syntax for a left outer join is:
<table-source> LEFT [OUTER] JOIN <table-source> ON <condition>
In other words: "OUTER" is an optional keyword (this is true for right
outer joins and full outer joins as well). Though I usually don't include
the OUTER keyword in my own code, I often do include it in newsgroups
postings, as it gives some extra documentation about what I'm doing.
Best, Hugo
Brrr-rrr... Checked books online and it says that yes, OUTER is optional.
But does it mean that it changes anything? As i understood - not really.
Correct?
Also i've seen there an example where just JOIN is used(w/o
INNER/LEFT/RIGHT/FULL).
But didn't really find any explanation of what's the difference? So can
you please explain what just JOIN does? Seems to me it should be same as
INNER JOIN... I'm confused...
Thank you,
Andrey
