The capability to link or join tables and generate one resultset from multiple tables is one of the most important characteristics of relational databases. A summary of join types is presented:
Inner join : For every row in the first table, SQL server goes through the second table trying to find a match based on the join column. Complexity of this algorithm= O(mn) (m= number of rows in first table, n=number of rows in second table)
Outer Join: An OUTER JOIN operation returns all rows that match the JOIN condition, and it may also return some of the rows that don’t match, depending on the type of OUTER JOIN used. There are three types of OUTER JOIN: RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN.
A RIGHT OUTER JOIN operation returns all matching rows in both tables, and also rows in the right table that don’t have a corresponding row in the left table.
A LEFT OUTER JOIN returns the rows from the left table that don’t have a corresponding row in the right table.
A FULL OUTER JOIN returns an intersection of a Right outer and left outer joins. A FULL OUTER JOIN operation returns:
- All rows that match the JOIN condition.
- Rows from the left table that don’t have a corresponding row in the right table. These rows have NULL values in the columns of the right table.
- Rows from the right table that don’t have a corresponding row in the left table. These rows have NULL values in the columns of the left table.
Cross join – Cartesian product of two tables. A Cross join with a condition can translate into an inner join
Self Join – A table joined with itself. Two copies of a table are created and merged.