As per the documentation: FROM (Transact-SQL):
For instance, the entire type-part of the
Points to be noted:
Source: https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server
<join_type> ::=
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
JOIN
The keyword OUTER
is marked as optional (enclosed in
square brackets), and what this means in this case is that whether you
specify it or not makes no difference. Note that while the other
elements of the join clause is also marked as optional, leaving them out will of course make a difference.For instance, the entire type-part of the
JOIN
clause is optional, in which case the default is INNER
if you just specify JOIN
. In other words, this is legal:SELECT *
FROM A JOIN B ON A.X = B.Y
Here's a list of equivalent syntaxes:A LEFT JOIN B A LEFT OUTER JOIN B
A RIGHT JOIN B A RIGHT OUTER JOIN B
A FULL JOIN B A FULL OUTER JOIN B
A INNER JOIN B A JOIN B
Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.At the top level there are mainly 3 types of joins:
- INNER
- OUTER
- CROSS
- INNER JOIN - fetches data if present in both the tables.
- OUTER JOIN are of 3 types:
LEFT OUTER JOIN
- fetches data if present in the left table.RIGHT OUTER JOIN
- fetches data if present in the right table.FULL OUTER JOIN
- fetches data if present in either of the two tables.
- CROSS JOIN, as the name suggests, does
[n X m]
that joins everything to everything.
Similar to scenario where we simply lists the tables for joining (in theFROM
clause of theSELECT
statement), using commas to separate them.
Points to be noted:
- If you just mention
JOIN
then by default it is aINNER JOIN
. - An
OUTER
join has to beLEFT
|RIGHT
|FULL
you can not simply sayOUTER JOIN
. - You can drop
OUTER
keyword and just sayLEFT JOIN
orRIGHT JOIN
orFULL JOIN
.
Reference documents:
What is an Outer Join? and (+) sign - SQL RIGHT JOIN Keyword
To combine two or more SELECT statements to form a single result table, use one of the following key words:
- UNION
- Returns all of the values from the result table of each SELECT statement. If you want all duplicate rows to be repeated in the result table, specify UNION ALL. If you want redundant duplicate rows to be eliminated from the result table, specify UNION or UNION DISTINCT.
- EXCEPT
- Returns all rows from the first result table (R1) that are not also in the second result table (R2). If you want all duplicate rows from R1 to be contained in the result table, specify EXCEPT ALL. If you want redundant duplicate rows in R1 to be eliminated from the result table, specify EXCEPT or EXCEPT DISTINCT.
- INTERSECT
- Returns rows that are in the result table of both SELECT statements. If you want all duplicate rows to be contained in the result table, specify INTERSECT ALL. If you want redundant duplicate rows to be eliminated from the result table, specify INTERSECT or INTERSECT DISTINCT.
This is good information to display.
ReplyDeleteGo pro alternative|Technofizi