Basically there are two types of joins which are supported by SQL:
I1. a. Implicit Joins
b. Explicit Joins 1. Implicit Joins :-
I have searched a lot about the sql joins and have found some interesting styles of declaring the joins in sql.
There's been a lot of talk about the deprecation of the "old style" (ANSI-92) JOIN syntax in SQL Server 2008. Consider the following old-style:
LEFT OUTER JOIN:
SELECT tableA.id, tableB.id
FROM tableA, tableB
WHERE tableA.id *= tableB.id
Right OUTER JOIN:
SELECT tableA.id, tableB.id
FROM tableA, tableB
WHERE tableA.id = tableB.id*
INNER JOIN:
SELECT tableA.id, tableB.id
FROM tableA, tableB
WHERE tableA.id = tableB.id
CROSS JOIN:
SELECT tableA.id, tableB.id
FROM tableA, tableB
The table citation portion of the query (FROM tableA, tableB) is ANSI standard and is NOT being deprecated. The LEFT OUTER JOIN syntax (*=) is not ANSI standard and IS deprecated as of SQL Server 2005.
So my old friend Brian can keep separating his tables with commas just as he's been doing.. but if he wants to do a LEFT or RIGHT OUTER JOIN, he'll need to use the "new" syntax.
2. Explicit Joins :-
Same as explicit joins are:
LEFT OUTER JOIN:
SELECT tableA.id, tableB.id
FROM tableA LEFT OUTER JOIN tableB on
tableA.id *= tableB.id
Right OUTER JOIN:
SELECT tableA.id, tableB.id
FROM tableA Right OUTER JOIN tableB on
tableA.id = tableB.id*
INNER JOIN:
SELECT tableA.id, tableB.id
FROM tableA INNER JOIN tableB on
tableA.id = tableB.id
CROSS JOIN:
SELECT tableA.id, tableB.id
FROM table CROSS JOIN tableB
FULL OUTER JOIN:
SELECT tableA.id, tableB.id
FROM tableA FULL OUTER JOIN tableB on
tableA.id = tableB.id