{"id":2251,"date":"2014-02-01T01:15:11","date_gmt":"2014-01-31T19:45:11","guid":{"rendered":"http:\/\/sqlhints.com\/?p=2251"},"modified":"2015-06-29T02:40:44","modified_gmt":"2015-06-28T21:10:44","slug":"joins-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2014\/02\/01\/joins-in-sql-server\/","title":{"rendered":"Joins In Sql Server"},"content":{"rendered":"

Sql Server Tutorial<\/a> Lesson 6: JOINS in Sql Server with Examples<\/h2>\n

Join facilitates the retrieval of information from multiple tables.\u00a0In Sql server we have following 6 types of Joins:<\/p>\n

    \n
  1. INNER JOIN<\/strong><\/li>\n
  2. LEFT OUTER JOIN<\/strong><\/li>\n
  3. RIGHT OUTER JOIN<\/strong><\/li>\n
  4. FULL OUTER JOIN<\/strong><\/li>\n
  5. CROSS JOIN<\/strong><\/li>\n
  6. SELF JOIN<\/strong><\/li>\n<\/ol>\n

    To demo these features let us first create the Customers and Orders table as depicted in the below image by using the following script:<\/p>\n

    \"Joins1\"<\/a><\/p>\n

    CREATE DATABASE SqlHintsJoinDemo\r\nGO\r\nUSE SqlHintsJoinDemo\r\nGO\r\n--Create Customers Table and Insert records\r\nCREATE TABLE Customers \r\n( CustomerId INT, Name VARCHAR(50) )\r\nGO\r\nINSERT INTO Customers(CustomerId, Name) VALUES(1,'Shree')\r\nINSERT INTO Customers(CustomerId, Name) VALUES(2,'Kalpana')\r\nINSERT INTO Customers(CustomerId, Name) VALUES(3,'Basavaraj')\r\nGO\r\n\r\n--Create Orders Table and Insert records into it\r\nCREATE TABLE Orders\r\n(OrderId INT, CustomerId INT, OrderDate DateTime)\r\nGO\r\nINSERT INTO Orders(OrderId, CustomerId, OrderDate) \r\nVALUES(100,1,Getdate()-1)\r\n\r\nINSERT INTO Orders VALUES(200,4,Getdate())\r\nINSERT INTO Orders VALUES(300,3,Getdate()+1)\r\nGO<\/pre>\n
    [ALSO READ] Joining Two Tables without any Common Column between them<\/a><\/strong><\/p>\n
    1. INNER JOIN in Sql Server<\/h2>\n
    Inner Join returns only the matching rows in both the tables (i.e. returns only those rows for which the join condition satisfies).<\/p>\n
    Demo 1:<\/strong> As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the Inner Join on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 only.<\/p>\n
    SELECT * \r\nFROM  Customers C \r\n\t\tINNER JOIN Orders O\r\n\t\t\tON O.CustomerId = C.CustomerId<\/pre>\n
    RESULT:<\/strong>
    \n    \"INNER<\/a><\/p>\n
    Demo 2:<\/strong> Below Inner Join query demonstrates how to get name of all the \u00a0Customer who have at-least one order in the Orders table.<\/p>\n
    SELECT C.Name \r\nFROM  Customers C \r\n\t\tINNER JOIN Orders O\r\n\t\t\tON O.CustomerId = C.CustomerId<\/pre>\n
    RESULT:<\/strong>
    \nName
    \n——————-
    \nBasavaraj
    \nShree<\/p>\n
    2. Left OUTER JOIN in Sql Server<\/h2>\n
    Left Outer Join\/Left Join returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right table doesn’t have the matching record then for such records right table column will have NULL value in the result.<\/p>\n
    Demo 1:<\/strong> As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the Left join on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 and for CustomerId 2 the Order Table columns will have NULL value in the result.<\/p>\n
    SELECT * \r\nFROM  Customers C \r\n\t\tLEFT OUTER JOIN Orders O\r\n\t\t\tON O.CustomerId = C.CustomerId<\/pre>\n
    RESULT:<\/strong>
    \n    \"LEFT<\/a><\/p>\n
    Demo 2:<\/strong> Below query demonstrates how to get the name of the Customer who don’t have Orders using LEFT OUTER JOIN.<\/p>\n
    SELECT C.CustomerId, C.Name \r\nFROM  Customers C \r\n\t\tLEFT OUTER JOIN Orders O\r\n\t\t\tON O.CustomerId = C.CustomerId\r\nWHERE O.OrderId IS NULL<\/pre>\n
    RESULT:<\/strong>
    \nCustomerId Name
    \n———– ————————————————–
    \n2 Kalpana<\/p>\n
    3. RIGHT OUTER JOIN in Sql Server<\/h2>\n
    Right Outer Join\/Right Join returns all the rows from the RIGHT table and the corresponding matching rows from the left table. If left table doesn’t have the matching record then for such records left table column will have NULL value in the result.<\/p>\n
    Demo 1:<\/strong> As per the data in our demo tables, only for the order with OrderId 200 we don’t have it’s corresponding customer info with CustomerId 4 in the Customers table. And for the other two orders, the corresponding customer info is present in the Customers Table. So for the orders with CustomerId 1 and 3 will have customer details and for the order with CustomerId 4, the Customers table columns will have NULL value in the result.<\/p>\n
    SELECT *\r\nFROM  Customers C \r\n\t\tRIGHT OUTER JOIN Orders O\r\n\t\t\tON O.CustomerId = C.CustomerId<\/pre>\n
    RESULT:<\/strong>
    \n    \"RIGHT<\/a><\/p>\n
    Demo 2:<\/strong> Below query demonstrates how to get the Orders with a CustomerId, for which we don’t have a mapping any record in the Customers Table:<\/p>\n
    SELECT O.*\r\nFROM  Customers C \r\n\t\tRIGHT OUTER JOIN Orders O\r\n\t\t\tON O.CustomerId = C.CustomerId\r\nWHERE C.CustomerId IS NULL<\/pre>\n
    RESULT:<\/strong>
    \nOrderId CustomerId OrderDate
    \n———– ———– ———————–
    \n200 4 2014-01-31 23:48:32.853<\/p>\n
    4. FULL OUTER JOIN in Sql Server<\/h2>\n
    It returns all the rows from both the tables, if there is no matching row in either of the sides\u00a0then it displays NULL values in the result for that table columns in such rows.<\/p>\n
    Full Outer Join = Left Outer Join + Right Outer Join<\/strong><\/p>\n
    Demo 1:<\/strong> As per the data in our Demo tables the Customer with CustomerId 2 doesn’t have order in the Orders table. So in the result of FULL Outer join between Customers and Orders table on the CustomerId column will have NULL values for the Orders table columns for the Customer with CustomerId 2.<\/p>\n
    And for the Order with OrderId 200 having CustomerId 4 doesn’t have a matching record in the customer table with CustomerId 4.\u00a0So in the result of FULL Outer join between Customers and Orders table on the CustomerId column will have NULL values for the Customers table columns for the Order with OrderId 200.<\/p>\n
    SELECT *\r\n\tFROM Customers C\r\n\t\t\tFULL OUTER JOIN Orders O\r\n\t\t\t\tON O.CustomerId = C.CustomerId<\/pre>\n
    RESULT:<\/strong>
    \n    \"FULL<\/a><\/p>\n
    Demo 2:<\/strong> Below query demonstrates how to get the list of all the Customers without Orders and also the Orders which doesn’t have corresponding customer in the Customers Table.<\/p>\n
    SELECT *\r\n\tFROM Customers C\r\n\t\t\tFULL OUTER JOIN Orders O\r\n\t\t\t\tON O.CustomerId = C.CustomerId\r\nWHERE C.CustomerId IS NULL OR O.OrderId IS NULL<\/pre>\n
    RESULT:<\/strong>
    \n    \"FULL<\/a><\/p>\n
    5. CROSS JOIN in Sql Server<\/h2>\n
    Cross join is also referred to as Cartesian Product. For every row in the LEFT Table of the CROSS JOIN all the rows from the RIGHT table are returned and Vice-Versa (i.e.result will have the Cartesian product of the rows from join tables).<\/p>\n
    No.of Rows in the Result of CRoss Join<\/strong> = (No. of Rows in LEFT Table<\/strong>) *<\/strong> (No. of Rows in RIGHT Table<\/strong>)<\/p>\n
    SELECT *\r\nFROM Customers C\r\n\t\tCROSS JOIN Orders O<\/pre>\n
    RESULT:<\/strong>
    \n    \"CROSS<\/a><\/p>\n
    6. SELF JOIN in Sql Server<\/h2>\n
    If a Table is joined to itself using one of the join types explained above, then such a type of join is called SELF JOIN.<\/p>\n
    To demo this join let us create an Employee table with data as depicted in the below image by the following script:<\/p>\n
    \"SELF<\/a><\/p>\n
    CREATE TABLE Employee\r\n(EmployeeId INT, Name NVARCHAR(50), ManagerId INT)\r\nGO\r\nINSERT INTO Employee VALUES(1,'Shree',1)\r\nINSERT INTO Employee VALUES(2,'Kalpana',1)\r\nINSERT INTO Employee VALUES(3,'Basavaraj',2) \r\nINSERT INTO Employee VALUES(4,'Monty',2) \r\nGO<\/pre>\n
    Demo 1:<\/strong> Now if we need to get the name of the Employee and his Manager name for each employee in the Employee Table. Then we have to Join Employee Table to itself as Employee and his Manager data is present in this table only as shown in the below query:<\/p>\n
    SELECT E.EmployeeId, \r\n       E.Name 'Employee Name', M.Name 'Manager Name' \r\nFROM dbo.Employee E \r\n\t\tINNER JOIN Employee M\r\n\t\t\tON M.EmployeeId = E.ManagerId<\/pre>\n
    RESULT:<\/strong>
    \n    \"SELF<\/a><\/p>\n
    [ALSO READ] Joining Two Tables without any Common Column between them<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
    Sql Server Tutorial Lesson 6: JOINS in Sql Server with Examples Join facilitates the retrieval of information from multiple tables.\u00a0In Sql server we have following 6 types of Joins: INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN CROSS JOIN SELF JOIN To demo these features let us first create the Customers and … Continue reading Joins In Sql Server<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3,242],"tags":[271,593,270,595,267,590,305,286,264,266,285,591,268,594,592,269,272,597,587,585,582,265,589,583,586,584,596,986,588,996],"class_list":["post-2251","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-sql-server-tutorial","tag-cross-join","tag-cross-join-sql","tag-full-outer-join","tag-full-outer-join-sql","tag-inner-join","tag-inner-join-sql","tag-join-between-two-tables","tag-joining-two-tables-with-examples","tag-joins","tag-joins-in-sql-server","tag-joins-with-examples","tag-left-join-sql","tag-left-outer-join","tag-outer-join-sql","tag-right-join-sql","tag-right-outer-join","tag-self-join","tag-self-join-sql","tag-sql-cross-join","tag-sql-full-outer-join","tag-sql-inner-join","tag-sql-joins","tag-sql-left-join","tag-sql-left-outer-join","tag-sql-outer-join","tag-sql-right-outer-join","tag-sql-self-join","tag-sql-server","tag-sql-server-joins","tag-sql-server-tutorial"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-Aj","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2251","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=2251"}],"version-history":[{"count":29,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2251\/revisions"}],"predecessor-version":[{"id":3452,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2251\/revisions\/3452"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=2251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=2251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=2251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}