SQL Server – SELECT Query & JOIN Types

  • Article

The notes taken while reading MCITP Self-Paced Training Kit (Exam 70-442): Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 (Self-Paced Training Kits).

From the book:

“The join relates tables based on a key column, such as primary key or a foreign key.”

JOIN Types

Performance tips from Chapter 14 — Improving SQL Server Performance related to joins:

“Try to avoid nullable foreign key columns to limit the amount of outer joins that might need to be written. Outer joins tend to be more expensive to process than inner joins. ”

“Be sure to create an index on any foreign key. Because foreign keys are used in joins, foreign keys almost always benefit from having an index.”

  • INNER JOIN – default join. All matching rows will be returned.

Example:

SQL INNER JOIN

  • FULL OUTER JOIN – this join returns all matching and un-matching rows from both tables.

Example:

SQL FULL OUTER JOIN

  • LEFT OUTER JOIN – this join returns all, either matching or un-matching, rows from the left table.

Example:

SQL LEFT OUTER JOIN

  • RIGHT OUTER JOIN – this join returns all, either matching or un-matching, rows from the right table.

Example:

SQL RIGHT OUTER JOIN

Examples are based on the sample AdventureWorks DB available here.

More Info

Special thanks go to my SQL mentor, SQL Customer Advisory Team member Jimmy May (blog|twitter)