Monday, 11 February 2013

SQL Server INNER JOIN Syntax and Example

SQL INNER JOIN 
The LEFT JOIN returns the matching records from the Left Table (Table_I) and the Right Table (Table_2).
The INNER JOIN  Query returns the when there is at least one match in both tables.

In other words it returns only the matching rows from both the tables.



Syntax:
SELECT Column_name(s) FROM Table_1 T1 INNER JOIN Table_2 T2
ON  T1.Column_name=T2.Column_name

Note: INNER JOIN is the same as JOIN.


Example :
Suppose we have two tables as follows

The "ORDERS" Table:
Order_Id
Sales_Period
Cust_Id
Sales
ABC_1234
Q1-2014
Cust_1234
40474
BCD_2345
Q2-2014
Cust_2345
44321
CDE_3456
Q3-2014
Cust_3456
19906
DEF_4567
Q4-2014
Cust_4567
34943
EFG_1122
Q1-2015
Cust_1122
22114
FGH_3344
Q2-2015
Cust_3344
41364
GHI_5566
Q3-2015
Cust_5566
16157
IJK_7788
Q4-2015
Cust_7788
36054

The "CUSTOMERS" Table:
Cust_Id
Cust_Name
Cust_Location
Cust_1234
ABC Soft Pvt 
Ltd
East
Cust_3456
CDE Info Pvt 
Ltd
West
Cust_4466
FFDD Info Pvt Ltd
Mid-West
Cust_1122
EFC Tech Pvt Ltd
South
Cust_6688
HHFF Tech Pvt Ltd
Mid-South
Cust_5566
GHI Services Pvt Ltd
North

Now we want to list all the Customers with any Orders.
We use the following Inner Join Query:
Select O.Order_Id,C.Cust_Name,C.Cust_Id,C.Cust_Location,O.Sales_Period,O.Sales from ORDERS INNER JOIN CUSTOMERS ON C.Cust_Id = O.Cust_Id

Output : 


Cust_Id
Cust_Name
Cust_Location
Order_Id
Sales_Period
Sales
Cust_1234
ABC Soft Pvt Ltd.
East
ABC_1234
Q1-2014
40474
Cust_3456
CDE Info Pvt Ltd
West
CDE_3456
Q3-2014
19906
Cust_1122
EFC Tech Pvt Ltd.
South
EFG_1122
Q1-2015
22114
Cust_5566
GHI Services Pvt Ltd.
North
GHI_5566
Q3-2015
16157

Notes :
--If there are rows in "Customers" that do not have matches in "Orders", those rows will NOT be listed.
--The following Inner Join Query returns the matching records and displays All the Columns from Orders table and only Cust_Name column from Customers Table
Select O.*,Cust_Name from Orders O
LEFT JOIN
Customers C
ON O.Cust_Id=C.Cust_Id
GO

--The following Inner Join Query returns the matching records and displays All the Columns from both the Orders table and Customers tables.
Select* from Orders O
LEFT JOIN
Customers C
ON O.Cust_Id=C.Cust_Id
GO

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts