19 October,2011 by Jack Vamvas
Outer Joins (LEFT JOIN, LEFT OUTER JOIN, RIGHT JOIN, RIGHT OUTER JOIN, FULL JOIN , FULL OUTER JOIN)
Cross Joins (aka Cartesian Join)
Joins are implemented in either the FROM or WHERE clause
JOINS are used to connect two tables or more based on a logical relationship
One example is a Parent – Foreign Key relationship.
Another example is using a logical operator , such as = or <> to define a relationship between values in columns .
Yes. Image, ntext , text. Although they can be joined indirectly via SUBSTRING
SELECT * FROM myTable1 JOIN myTable2 ON SUBSTRING(myTable1.textCol, 1, 10) = SUBSTRING(myTable2.textCol,1,10)
When NULLS exist in columns and an INNER JOIN is used , NULLS will not be matched.
Nulls can only be returned by an OUTER JOIN. If they’re excluded with the WHERE clause, then they won’t be returned
The ISO 99 Standard supports the INNER JOIN in the FROM
The resultset of a CROSS JOIN without a WHERE clause. The resultset count will be the number of rows in the first table multipled by every row in the second table.
If 1,000,000 rows exist in the first table and 100 exist in the second the resultset count is 100,000,000
The Cartesian product is named after the Renee Descartes and is based on his work on analytic geometry
Yes , as long as one of the table references uses a table alias. These types of JOINS are called self-join.
Some examples are :
a) self reference
b) find duplicates . An alternative is ROW_NUMBER() ranking function