Programmer's Rules

SQL Server

Fundamentals of joins in SQL Server

Overview of join types in SQL Server

SQL Serverjob interview70-761

In the logical order of the SELECT statement the FROM clause is the first to be executed. This phase produces a virtual table (in logical sense - not physical) which is subsequently used by other phases of the SELECT statement, such as the WHERE clause. When using joins rows are added to the above virtual table is or deleted from it.

There are 3 types of joins in SQL Server:

1. Inner join - denoted in 2 possible versions - INNER JOIN or JOIN.

Returns only rows where match is found in both input tables. Inner join first creates the Cartesian product (joins every row of one input table to all rows of the second input table). Then, depending on the ON clause the rows are filtered, removing not fitting rows.

2. Outer join (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN).

Returns rows from one table and any matching rows from the second table. Outer join first creates the Cartesian product then filters the rows and removing from virtual table. Next all rows from one table are added back to the virtual table. NULLS are displayed when match is not found.

3. Cross join (CROSS JOIN).

Combines each row from the first table with each row from the second table. Adds all combinations of then input tables to the virtual table.
Tutorial statistics