Working of Outer Join in SQL Server

Question

Hi friends I want to know how to work with Outer join in SQL server. Please explain it with example. Thank you.

Satyapriya Nayak · Answer

Hi Vipendra, SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2). SQL LEFT JOIN Syntax SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name PS: In some databases LEFT JOIN is called LEFT OUTER JOIN. SQL LEFT 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 and their orders - if any, from the tables above. We use the following SELECT statement: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT 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 Svendson Tove The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders). SQL RIGHT JOIN Keyword The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1). SQL RIGHT JOIN Syntax SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN. SQL RIGHT 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 orders with containing persons - if any, from the tables above. We use the following SELECT statement: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT 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 34764 The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons). SQL FULL JOIN Keyword The FULL JOIN keyword return rows when there is a match in one of the tables. SQL FULL JOIN Syntax SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name SQL FULL 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 and their orders, and all the orders with their persons. We use the following SELECT statement: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL 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 Svendson Tove 34764 The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well. Please refer the below links http://www.w3schools.com/sql/sql_join_left.asp http://www.w3schools.com/sql/sql_join_right.asp http://www.w3schools.com/sql/sql_join_full.asp Thanks

Jignesh Trivedi · Answer

hi, Please refer http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx it very good article on same. hope this help.

MuthuMari M · Answer

Hi, Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join pls refer this url for sample queries : http://msdn.microsoft.com/en-us/library/aa213228%28v=sql.80%29.aspx thanks. If this post is useful then mark it as " Accepted Answer "

Senthilkumar · Answer

Hi Viependra, OUTER JOIN is used to join two tables even if there is not a match. An OUTER JOIN can be used to return a list of all the customers and the orders even if no orders have been placed for some of the customers. A keyword, RIGHT or LEFT, is used to specify which side of the join returns all possible rows. I like using LEFT because it makes sense to me to list the most important table first. Except for one example demonstrating RIGHT OUTER JOIN, this article will use left joins. Just a note: the keywords INNER and OUTER are optional. SQL Server uses the following ISO keywords for outer joins specified in a FROM clause: LEFT OUTER JOIN or LEFT JOIN RIGHT OUTER JOIN or RIGHT JOIN FULL OUTER JOIN or FULL JOIN Please visit this web url for more examples, http://msdn.microsoft.com/en-us/library/ms187518.aspx http://www.sqlteam.com/article/writing-outer-joins-in-t-sql http://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/

Working of Outer Join in SQL Server

Answers (4)