{"id":711,"date":"2011-08-31T19:34:51","date_gmt":"2011-08-31T19:34:51","guid":{"rendered":"http:\/\/craftydba.com\/?p=711"},"modified":"2011-09-01T13:31:26","modified_gmt":"2011-09-01T13:31:26","slug":"selecting-data-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=711","title":{"rendered":"Selecting Data – Part 2"},"content":{"rendered":"

The SELECT <\/a>reserved word is part of the Data Manipulation Language (DML) defined by Codd <\/a>and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data.<\/p>\n

Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using joins and unions.<\/p>\n

Six known join types exist in SQL Server. The definitions are based upon joining TABLE A (left) with TABLE B (right).<\/p>\n

    \n
  1. Inner Join <\/a>– Include matching rows from left and right tables equal joined on a key.<\/li>\n
  2. Left Outer Join <\/a>– Include all rows from left table and any matching rows from right table. Unmatched right rows are shown as null values.<\/li>\n
  3. Right Outer Join <\/a>– Include all rows from right table and any matching rows from left table. Unmatched left rows are shown as null values.<\/li>\n
  4. Full Outer Join <\/a>– Include all rows from left and right tables. Unmatched rows are shown as null values.<\/li>\n
  5. Theta Join <\/a>– Match rows between left and right tables using not-equal condition (<, >, <=, >=, <>).<\/li>\n
  6. Cross Join <\/a>– This is known as a cartesian product. Each row in left table is matched with all rows in right without any conditions.<\/li>\n<\/ol>\n

    We will be creating a simple AUTO database with two tables to show how joins work. TABLE A has cars that Alan owns and TABLE B has cars that John owns. This example does not conform to Normalization rules but defines primary keys for joining between the two tables. In the real world, one table would have a PRIMARY <\/a>key and the other, a FOREIGN <\/a>key.<\/p>\n

    The code snipet below creates this teaching database and populates it with data.<\/p>\n

    -- Create a very basic database\r\nCREATE DATABASE AUTOS;\r\nGO\r\n\r\n-- Use the database\r\nUSE [AUTOS]\r\nGO\r\n\r\n-- Person A Cars\r\nCREATE TABLE A\r\n(\r\nMyId INT PRIMARY KEY CLUSTERED,\r\nMyValue VARCHAR(20)\r\n)\r\nGO\r\n\r\nINSERT INTO A (MyId, MyValue) VALUES\r\n(1, 'Continental'),\r\n(2, 'Edsel'),\r\n(3, 'Lincoln'),\r\n(4, 'Mercury'),\r\n(5, 'Ram')\r\nGO\r\n\r\n-- Person B Cars\r\nCREATE TABLE B\r\n(\r\nMyId INT PRIMARY KEY CLUSTERED,\r\nMyValue VARCHAR(20)\r\n)\r\nGO\r\n\r\nINSERT INTO B (MyId, MyValue) VALUES\r\n(1, 'Continental'),\r\n(4, 'Mercury'),\r\n(5, 'Ram'),\r\n(6, 'Jeep'),\r\n(7, 'Plymouth'),\r\n(8, 'Dodge')\r\nGO<\/span><\/pre>\n<\/p>\n
    A venn diagram is an easy way to represent set operations. Click on the links below to see diagrams for selected joins.  The empty set<\/a> is when no records are in common between SET T1 and SET T2.  <\/p>\n
    A INNER JOIN<\/a> finds the records in common between TABLE A and TABLE B.<\/p>\n
    -- Inner Join - Find cars A & B have in common\r\nSELECT * FROM A INNER JOIN B ON A.MyId = B.MyId\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue              MyId        MyValue\r\n----------- -------------------- ----------- --------------------\r\n1            Continental          1           Continental\r\n4            Mercury              4           Mercury\r\n5            Ram                  5           Ram<\/span><\/pre>\n<\/p>\n
    <\/span>A LEFT OUTER JOIN <\/a>list all records that TABLE A has and matching records from TABLE B.<\/p>\n
    -- Left Join - Find cars A owns but B does not\r\nSELECT * FROM A LEFT OUTER JOIN B ON A.MyId = B.MyId WHERE B.MyId IS NULL\r\nGO\r\n\r\n-- Output\r\nMyId        MyValue              MyId        MyValue\r\n----------- -------------------- ----------- --------------------\r\n2           Edsel                NULL        NULL\r\n3           Lincoln              NULL        NULL<\/span><\/pre>\n<\/p>\n
    A RIGHT OUTER JOIN <\/a>list alls records that TABLE B has and matching records from TABLE A.<\/p>\n
    -- Right Join - Find cars B owns but A does not\r\nSELECT * FROM A RIGHT OUTER JOIN B ON A.MyId = B.MyId WHERE A.MyId IS NULL\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue              MyId        MyValue\r\n----------- -------------------- ----------- --------------------\r\nNULL         NULL                 6           Jeep\r\nNULL         NULL                 7           Plymouth\r\nNULL         NULL                 8           Dodge<\/span><\/pre>\n<\/p>\n
    A FULL OUTER JOIN <\/a>is basically the UNION of a LEFT and RIGHT outer join with duplicates removed.<\/p>\n
    -- Full Outer Join - Show results of left and right join merged\r\nSELECT * FROM A FULL OUTER JOIN B ON A.MyId = B.MyId\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue              MyId        MyValue\r\n----------- -------------------- ----------- --------------------\r\n1            Continental          1           Continental\r\n2            Edsel                NULL        NULL\r\n3            Lincoln              NULL        NULL\r\n4            Mercury              4           Mercury\r\n5            Ram                  5           Ram\r\nNULL         NULL                 6           Jeep\r\nNULL         NULL                 7           Plymouth\r\nNULL         NULL                 8           Dodge<\/span><\/pre>\n<\/p>\n
    A CROSS JOIN is the cartesian product of TABLE A and TABLE B.<\/p>\n
    -- Cross Join - Join each row in A with a row in B\r\nSELECT * FROM A CROSS JOIN B\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue              MyId        MyValue\r\n----------- -------------------- ----------- --------------------\r\n1            Continental          1           Continental\r\n1            Continental          4           Mercury\r\n1            Continental          5           Ram\r\n1            Continental          6           Jeep\r\n1            Continental          7           Plymouth\r\n1            Continental          8           Dodge\r\n2            Edsel                1           Continental\r\n2            Edsel                4           Mercury\r\n2            Edsel                5           Ram\r\n2            Edsel                6           Jeep\r\n2            Edsel                7           Plymouth\r\n2            Edsel                8           Dodge\r\n3            Lincoln              1           Continental\r\n3            Lincoln              4           Mercury\r\n3            Lincoln              5           Ram\r\n3            Lincoln              6           Jeep\r\n3            Lincoln              7           Plymouth\r\n3            Lincoln              8           Dodge\r\n4            Mercury              1           Continental\r\n4            Mercury              4           Mercury\r\n4            Mercury              5           Ram\r\n4            Mercury              6           Jeep\r\n4            Mercury              7           Plymouth\r\n4            Mercury              8           Dodge\r\n5            Ram                  1           Continental\r\n5            Ram                  4           Mercury\r\n5            Ram                  5           Ram\r\n5            Ram                  6           Jeep\r\n5            Ram                  7           Plymouth\r\n5            Ram                  8           Dodge<\/span><\/pre>\n<\/p>\n
    A THETA JOIN is when the join condition is a non-equality. I have never used this join in the REAL WORLD; However, If you worked at Chucky Cheese and wanted to see all the items that your kid could get for earned game points, this join might come in handy. From the KIDS table, earned points would be a key that would be joined to the PRIZES table on point value where prize point value <= earn points. This query would give you the prize pool to pick from.<\/p>\n
    -- Theta Join - Not used often SELECT * FROM A JOIN B ON A.MyId >= B.MyId\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue              MyId        MyValue\r\n----------- -------------------- ----------- --------------------\r\n1            Continental          1           Continental\r\n2            Edsel                1           Continental\r\n3            Lincoln              1           Continental\r\n4            Mercury              1           Continental\r\n4            Mercury              4           Mercury\r\n5            Ram                  1           Continental\r\n5            Ram                  4           Mercury\r\n5            Ram                  5           Ram<\/span><\/pre>\n<\/p>\n
    The UNION reserved word is used to join two simular sets of columns from two or more tables.<\/p>\n
    -- Combine two lists as one, dupes removed\r\nSELECT * FROM A\r\nUNION\r\nSELECT * FROM B\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue\r\n----------- --------------------\r\n1           Continental\r\n2           Edsel\r\n3           Lincoln\r\n4           Mercury\r\n5           Ram\r\n6           Jeep\r\n7           Plymouth\r\n8           Dodge<\/span><\/pre>\n<\/p>\n
    The default behavoir of the word is to remove duplicates. If you want to keep duplicates, add the ALL reserved word.<\/p>\n
    -- Combine two lists as one, dupes keep\r\nSELECT * FROM A\r\nUNION ALL\r\nSELECT * FROM B\r\nORDER BY MyId\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue\r\n----------- --------------------\r\n1           Continental\r\n1           Continental\r\n2           Edsel\r\n3           Lincoln\r\n4           Mercury\r\n4           Mercury\r\n5           Ram\r\n5           Ram\r\n6           Jeep\r\n7           Plymouth\r\n8           Dodge<\/span><\/pre>\n<\/p>\n
    The EXCEPT and INTERSECT reserved words are used to merge and compare records as a whole. You probably do not realize we did just this with the LEFT and RIGHT joins on key values.<\/p>\n
    The two snipets below use the EXCEPT clause to return unique cars.<\/p>\n
    -- What A has unique\r\nSELECT * FROM A\r\nEXCEPT\r\nSELECT * FROM B\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue\r\n----------- --------------------\r\n2           Edsel\r\n3           Lincoln\r\n\r\n-- What B has unique\r\nSELECT * FROM B\r\nEXCEPT\r\nSELECT * FROM A\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue\r\n----------- --------------------\r\n6           Jeep\r\n7           Plymouth\r\n8           Dodge<\/span><\/pre>\n<\/p>\n
    The snipet below uses the INTERSECT clause to return common cars.<\/p>\n
    -- Common fields based on whole record, not just key\r\nSELECT * FROM A\r\nINTERSECT\r\nSELECT * FROM B\r\nGO\r\n\r\n-- Output\r\nMyId            MyValue\r\n-----------    --------------------\r\n1               Continental\r\n4               Mercury\r\n5               Ram<\/span><\/pre>\n<\/p>\n
    To demonstate that the whole record, not just the PRIMARY key is used in the compare, I am going to change one column to the value test. As we can see, the results change from above. The snipet below restores the data to leave our example intack.<\/p>\n
    -- Change one record in table A\r\nUPDATE A\r\nSET MyValue = 'test'\r\nWHERE MyId = 1\r\nGO\r\n\r\n-- Different results\r\nSELECT * FROM A\r\nINTERSECT\r\nSELECT * FROM B\r\nGO\r\n\r\n-- Restore value to one record\r\nUPDATE A\r\nSET MyValue = 'Continental'\r\nWHERE MyId = 1\r\nGO\r\n\r\n-- Output\r\nMyId         MyValue\r\n-----------  --------------------\r\n4            Mercury\r\n5            Ram<\/span><\/pre>\n<\/p>\n
    To recap this discussion, JOINS are used to merge data horizontally by adding additional fields to the result set. UNIONS are used to merger the data vertically by stacking simular data together. The EXCEPT and INTERSECT operations return what is different and common between records from two tables. In short, these key words allow you to take NORMALIZED data combine it to return results that are important to your organization.<\/p>\n
    Next time I talk about the SELECT statement, I will be introducing functions that can be applied to the various datatypes.<\/p>\n","protected":false},"excerpt":{"rendered":"
    The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using joins and unions. Six known join types exist in SQL Server. The definitions are based upon joining TABLE A (left) with TABLE B (right). Inner Join – Include…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[86,31,90,12,85,82,91,15,87,83,84,37,28,92,29,88,89],"class_list":["post-711","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-cross-join","tag-database-developer","tag-except","tag-free-code","tag-full-outer-join","tag-inner-join","tag-intersect","tag-john-f-miner-iii","tag-join","tag-left-join","tag-right-join","tag-select","tag-sql-server","tag-theta-join","tag-tsql","tag-union","tag-union-all"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/711","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=711"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/711\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=711"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=711"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=711"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}