page contents

About the Post

Author Information

Types of MSSQL Joins

In Microsoft SQL Server, we have four types of joins. We use these joins to fetch data from multiple tables that are based on a condition. A join condition is a relationship among some columns in the data tables that take part in SQL join. Basically data tables are related to each other with keys. We use these keys relationship in SQL joins.

Joining tables to obtain needed data for a query, script or stored procedure is a key concept with Microsoft SQL Server. Simply put, joins are typically performed in the FROM clause of a table or view for the SELECT, INSERT…SELECT, SELECT…INTO, UPDATE and DELETE statements.

Inner Join

 

Inner join will only return those records/rows that match/exists in both tables.

It will match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data. Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity. In general the INNER JOIN option is considered to be the most common join needed in applications and/or queries. Although most join logic is based on matching values between the two columns specified, it is possible to also include logic using greater than, less than, not equals, etc.

Outer Join (3 types)

  • Left Outer Join

Left outer join returns all records/rows from the left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

Based on the two tables specified in the join clause, all data is returned from the left table. On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table. Another item to keep in mind is that the LEFT and RIGHT OUTER JOIN logic is opposite
of one another. That means that you can change either the order of the tables in the specific join statement or change the JOIN from left to right or vice versa and get the same results.

  • Right Outer Join

Right outer join returns all records/rows from the right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values.

RIGHT OUTER JOIN – Based on the two tables specified in the join clause, all data is returned from the right table. On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.

  • Full Outer Join

Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables. If there are no columns matching in the both tables, it returns NULL values.

Cross Join

 

Cross join is a cartesian join which means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table.

Again, this is based on the two tables specified in the join clause, a Cartesian product is created if a WHERE clause does filter the rows. The size of the Cartesian product is based on multiplying the number of rows from the left table by the number of rows in the right table. Please heed caution when using a CROSS JOIN.

Self Join

 

Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self joins are not really a type of SQL join. To list a table two times in the same query, you must
provide a table alias for at least one of instance of the table name. This table alias helps the query processor determine whether columns should present data from the right or left version of the table.

Tags: , , , , ,

One Response to “Types of MSSQL Joins”

  1. Fantastic web-site. Plenty of very helpful data in this article. Now i’m sending it to a couple close friends ans in addition giving with yummy. Not to mention, thank you with your sebaceous!

    July 5, 2014 at 5:06 AM
Copy Protected by Chetan's WP-Copyprotect.