Inner Joins in SQL Server

Question

Hi friends I want to know how to work with inner joins in SQL Server. Please explain it with example. Thank you

Satyapriya Nayak · Answer

Hi Vipendra SQL INNER JOIN The INNER JOIN keyword return rows when there is at least one match in both tables. SQL INNER JOIN Syntax SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name PS: INNER JOIN is the same as JOIN. SQL INNER JOIN Example The Persons table: P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger The Orders table: O_Id OrderNo P_Id 1 77895 3 2 44678 3 3 22456 1 4 24562 1 5 34764 15 Now we want to list all the persons with any orders. We use the following SELECT statement: SELECT Persons.LastName Persons.FirstName Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName The result-set will look like this: LastName FirstName OrderNo Hansen Ola 22456 Hansen Ola 24562 Pettersen Kari 77895 Pettersen Kari 44678 The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in Persons that do not have matches in Orders those rows will NOT be listed. Please refer the below link http://www.w3schools.com/sql/sql_join_inner.asp Thanks

Vipendra Verma · Answer

Thank you

Senthilkumar · Answer

Hi vipendra The inner join is used to join one or more tables and returns the results from various table when condition matches. Lets see what is inner join InnerJoin is used to join one or more tables in the sql server. It will returns the matching records in the both table based on the condition. Assume that there are two tables: Products and Sales Products: ProductID | Name | Manfacturer | Rate | Date ------------------------------------------------------- 1 Scooty TVS 35000 10/10/2012 2 Splendor Hero Honda 50000 5/6/2012 Sales: SaleID | CustomerID | ProductID | Qty | SoldDate ------------------------------------------------------ 1 1 2 1 12/12/2012 Here i want to join these two tables. It will return the matching records in both tables. SELECT S.SaleID P.ProductID P.ProductName P.Manfacturer S.CustomerID S.SoldDate S.QtyP.Rate (s.Qty * P.Rate) AS [Total] FROM Products P INNER JOIN Sales S ON s.ProductID = P.ProductID The above query returns the result set as SaleID | ProductID | ProductName | Manfacturer | CustomerID | SoldDate | Qty | Rate | Total --------------------------------------------------------------------------------------------------------- 1 2 Splendor Hero Honda 1 12/12/2010 1 50000 50000.00 Still you can join the customer table for display the custom details with S.CustomerID = Customer.CustomerID. Hope this will help you.