Wednesday, 23 October 2013

What is the Difference between SQL Server Inner Join and Outer Join

SQL SERVER JOIN Clause :
The SQL JOIN is a clause that enables a SELECT statement to access more than one table. The JOIN clause controls how tables are linked. It is a qualifier of the SQL FROM clause.
In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use Primary Key of first table and Foreign Key of secondary table to get data from tables, by using this relationship we can reduce the duplication of data in every table.

Why and How to use JOIN Clause :
Use the SQL JOIN whenever multiple tables must be accessed through a SQL SELECT statement and no results should be returned if there is not a match between the Joined tables.The ON clause describes the conditions of the JOIN.

Syntax :
SELECT <column_name1>, <column_name2> <aggregate_function> FROM <table_name> JOIN <table_name> ON <join_conditions>

Example : 
Let us suppose there are two tables called CUSTOMERS and ORDERS as follows

Customers Table 
CREATE TABLE Customers(Cid INT PRIMARY KEY IDENTITY(111,1), 
Cname VARCHAR(15), Location VARCHAR(15));

INSERT INTO Customers VALUES ('Abc','Hyderabad'),('Bcd','Bangalore'),('Cde','Chennai') ,('Efg','Pune'), ('Fgh','Hyderabad'),('Ghi','Bangalore'),('Ijk','Bangalore');

Select * From Customers  



Orders Table
CREATE TABLE Orders(Oid INT IDENTITY(1234,3),Prouduct VARCHAR(15),Cid INT);

INSERT INTO Orders VALUES('LapTop',113),('TeleVision',115),('Cooler',117),
('Fridge',143),('Pen Drive',789);

Select * From Orders

Now we perform the join on the above tables as follows 
SELECT C.Cid,C.Cname,C.Location,O.Oid,O.Product FROM 
CUSTOMERS C JOIN Orders O ON C.Cid=O.C_Id ;

This join is also known as Normal Join or Inner Join.The result of the Join is as follows :



Inner Join Vs Outer Join
The standard JOIN clause (also known as the INNER JOIN clause) returns the rows only when there are matches for the JOIN criteria on the second table.If there are no matches on the JOIN criteria then no rows will be returned. 

An INNER JOIN should be used only when you want to pull data that exists in both of the tables. If any entry found in the first table, and it does not have a matching entry in the second table, then that record will not be included in the result set.

An OUTER JOIN should be used when you want to pull all of the matching data in the first table, regardless of whether or not it exists in the second table. Your result set will contain NULL for the rows that don't have the matching records in the second table.

Notes :
A "Cartesian product" can result if there is no relation between the tables for the join. A row would be included for each combination between the two tables so if one table has 1,000 rows and the second table has 2,000 rows then 2,000,000 rows would be returned.

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts