{"id":1961,"date":"2019-08-09T06:33:49","date_gmt":"2019-08-09T06:33:49","guid":{"rendered":"https:\/\/www.testpreptraining.com\/tutorial\/?page_id=1961"},"modified":"2020-05-02T08:30:24","modified_gmt":"2020-05-02T08:30:24","slug":"query-multiple-tables-by-using-joins","status":"publish","type":"page","link":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/","title":{"rendered":"Query Multiple Tables by using Joins"},"content":{"rendered":"\n

SQL Join<\/strong><\/h3>\n\n\n\n

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Let’s us now take a look at a selection from the “Orders” table –<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Then, look at a selection from the “Customers” table: <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column.<\/p>\n\n\n\n

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables – <\/p>\n\n\n\n

SQL Statement<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Result – <\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This produces something like this – <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Different type of SQL Joins<\/strong><\/p>\n\n\n\n

Here are the different types of the JOINs in SQL:<\/p>\n\n\n\n

  • (INNER) JOIN<\/strong>: Returns records that have matching values in both tables<\/li>
  • LEFT (OUTER) JOIN<\/strong>: Returns all records from the left table, and the matched records from the right table<\/li>
  • RIGHT (OUTER) JOIN<\/strong>: Returns all records from the right table, and the matched records from the left table<\/li>
  • FULL (OUTER) JOIN<\/strong>: Returns all records when there is a match in either left or right table<\/li><\/ul>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    The INNER JOIN keyword selects records that have matching values in both tables.<\/p>\n\n\n\n

    INNER JOIN Syntax<\/em><\/strong><\/p>\n\n\n\n

    SELECT column_name(s)
    FROM table1
    INNER JOIN table2
    ON table1.column_name = table2.column_name;<\/p>\n\n\n\n

    \"\"<\/figure><\/div>\n\n\n\n

    SQL LEFT JOIN Keyword<\/strong><\/p>\n\n\n\n

    The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.<\/p>\n\n\n\n

    LEFT JOIN Syntax<\/strong><\/em><\/p>\n\n\n\n

    SELECT column_name(s)
    FROM table1
    LEFT JOIN table2
    ON table1.column_name = table2.column_name;<\/p>\n\n\n\n

    Note: <\/strong>In some databases LEFT JOIN is called LEFT OUTER JOIN.<\/p>\n\n\n\n

    \"\"<\/figure><\/div>\n\n\n\n

    SQL RIGHT JOIN Keyword<\/strong><\/p>\n\n\n\n

    The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.<\/p>\n\n\n\n

    RIGHT JOIN Syntax<\/em><\/strong><\/p>\n\n\n\n

    SELECT column_name(s)
    FROM table1
    RIGHT JOIN table2
    ON table1.column_name = table2.column_name;
    Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.<\/p>\n\n\n\n

    SQL FULL OUTER JOIN Keyword<\/strong><\/p>\n\n\n\n

    The FULL OUTER JOIN keyword return all records when there is a match in left (table1) or right (table2) table records.<\/p>\n\n\n\n

    Note: FULL OUTER JOIN can potentially return very large result-sets. Also FULL OUTER JOIN and FULL JOIN are the same.<\/p>\n\n\n\n

    FULL OUTER JOIN Syntax<\/em><\/strong><\/p>\n\n\n\n

    SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column_name = table2.column_name
    WHERE condition;<\/p>\n\n\n\n

    \"\"<\/figure><\/div>\n\n\n\n

    SQL Self JOIN<\/strong><\/p>\n\n\n\n

    A self JOIN is a regular join, but the table is joined with itself.<\/p>\n\n\n\n

    Self JOIN Syntax<\/em><\/strong><\/p>\n\n\n\n

    SELECT column_name(s)
    FROM table1 T1, table1 T2
    WHERE condition;<\/p>\n\n\n\n

    UNION ALL Syntax<\/strong><\/p>\n\n\n\n

    The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:<\/p>\n\n\n\n

    SELECT column_name(s) FROM table1
    UNION ALL
    SELECT column_name(s) FROM table2;
    Note<\/strong>: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.<\/p>\n\n\n\n

    The SQL UNION Operator<\/strong><\/p>\n\n\n\n

    The UNION operator is used to combine the result-set of two or more SELECT statements.<\/p>\n\n\n\n

    • Each SELECT statement within UNION must have the same number of columns<\/li>
    • The columns must also have similar data types<\/li>
    • The columns in each SELECT statement must also be in the same order <\/li><\/ul>\n\n\n\n

      UNION Syntax<\/strong><\/p>\n\n\n\n

      SELECT column_name(s) FROM table1
      UNION
      SELECT column_name(s) FROM table2;<\/p>\n\n\n\n

      UNION ALL Syntax<\/em><\/strong><\/p>\n\n\n\n

      The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL –<\/p>\n\n\n\n

      SELECT column_name(s) FROM table1
      UNION ALL
      SELECT column_name(s) FROM table2;<\/p>\n\n\n\n

      Note: <\/strong>The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.<\/p>\n\n\n\n

      SQL GROUP BY Statement<\/strong><\/p>\n\n\n\n

      The GROUP BY statement group rows that have the same values into summary rows, like “find the number of customers in each country”.<\/p>\n\n\n\n

      The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.<\/p>\n\n\n\n

      GROUP BY Syntax<\/em><\/strong><\/p>\n\n\n\n

      SELECT column_name(s)
      FROM table_name
      WHERE condition
      GROUP BY column_name(s)
      ORDER BY column_name(s);<\/p>\n\n\n\n

      SQL HAVING Clause<\/strong><\/p>\n\n\n\n

      The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.<\/p>\n\n\n\n

      HAVING Syntax<\/strong><\/p>\n\n\n\n

      SELECT column_name(s)
      FROM table_name
      WHERE condition
      GROUP BY column_name(s)
      HAVING condition
      ORDER BY column_name(s);<\/p>\n\n\n\n

      SQL EXISTS Operator<\/strong><\/p>\n\n\n\n

      The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns true if the subquery returns one or more records.<\/p>\n\n\n\n

      EXISTS Syntax<\/strong><\/p>\n\n\n\n

      SELECT column_name(s)
      FROM table_name
      WHERE EXISTS
      (SELECT column_name FROM table_name WHERE condition);<\/p>\n","protected":false},"excerpt":{"rendered":"

      SQL Join A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Let’s us now take a look at a selection from the “Orders” table – Then, look at a selection from the “Customers” table: Notice that the “CustomerID” column in the “Orders” table refers…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1931,"menu_order":2,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"footnotes":""},"categories":[],"tags":[360,358,357,362,359],"class_list":["post-1961","page","type-page","status-publish","hentry","tag-exam-70-761","tag-microsoft-70-761","tag-microsoft-exam","tag-query-multiple-table-by-using-joins","tag-querying-data-with-transact-sql"],"acf":[],"yoast_head":"\nQuery Multiple Tables by using Joins - Testprep Training Tutorials<\/title>\n<meta name=\"description\" content=\"Get ready to boost your learning and prepare for Query Multiple Tables by using Joins. Boost your chances become Microsoft 70-761 exam Now!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Multiple Tables by using Joins - Testprep Training Tutorials\" \/>\n<meta property=\"og:description\" content=\"Get ready to boost your learning and prepare for Query Multiple Tables by using Joins. Boost your chances become Microsoft 70-761 exam Now!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/\" \/>\n<meta property=\"og:site_name\" content=\"Testprep Training Tutorials\" \/>\n<meta property=\"article:modified_time\" content=\"2020-05-02T08:30:24+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2019\/08\/image-91-750x119.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/\",\"name\":\"Query Multiple Tables by using Joins - Testprep Training Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\"},\"datePublished\":\"2019-08-09T06:33:49+00:00\",\"dateModified\":\"2020-05-02T08:30:24+00:00\",\"description\":\"Get ready to boost your learning and prepare for Query Multiple Tables by using Joins. Boost your chances become Microsoft 70-761 exam Now!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Exam 70-761: Querying Data with Transact-SQL\",\"item\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Query Multiple Tables by using Joins\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"name\":\"Testprep Training Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\",\"name\":\"Testprep Training\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"contentUrl\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"width\":583,\"height\":153,\"caption\":\"Testprep Training\"},\"image\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Query Multiple Tables by using Joins - Testprep Training Tutorials","description":"Get ready to boost your learning and prepare for Query Multiple Tables by using Joins. Boost your chances become Microsoft 70-761 exam Now!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/","og_locale":"en_US","og_type":"article","og_title":"Query Multiple Tables by using Joins - Testprep Training Tutorials","og_description":"Get ready to boost your learning and prepare for Query Multiple Tables by using Joins. Boost your chances become Microsoft 70-761 exam Now!","og_url":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/","og_site_name":"Testprep Training Tutorials","article_modified_time":"2020-05-02T08:30:24+00:00","og_image":[{"url":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2019\/08\/image-91-750x119.png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/","url":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/","name":"Query Multiple Tables by using Joins - Testprep Training Tutorials","isPartOf":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website"},"datePublished":"2019-08-09T06:33:49+00:00","dateModified":"2020-05-02T08:30:24+00:00","description":"Get ready to boost your learning and prepare for Query Multiple Tables by using Joins. Boost your chances become Microsoft 70-761 exam Now!","breadcrumb":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/query-multiple-tables-by-using-joins\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.testpreptraining.ai\/tutorial\/"},{"@type":"ListItem","position":2,"name":"Exam 70-761: Querying Data with Transact-SQL","item":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/"},{"@type":"ListItem","position":3,"name":"Query Multiple Tables by using Joins"}]},{"@type":"WebSite","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","name":"Testprep Training Tutorials","description":"","publisher":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization","name":"Testprep Training","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/","url":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","contentUrl":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","width":583,"height":153,"caption":"Testprep Training"},"image":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/1961","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/comments?post=1961"}],"version-history":[{"count":3,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/1961\/revisions"}],"predecessor-version":[{"id":5335,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/1961\/revisions\/5335"}],"up":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/1931"}],"wp:attachment":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/media?parent=1961"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/categories?post=1961"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/tags?post=1961"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}