{"id":2155,"date":"2015-04-07T08:06:12","date_gmt":"2015-04-07T15:06:12","guid":{"rendered":"http:\/\/www.wellgolly.com\/?p=2155"},"modified":"2015-04-07T08:06:26","modified_gmt":"2015-04-07T15:06:26","slug":"mysql-example-outer-joins","status":"publish","type":"post","link":"https:\/\/www.wellgolly.com\/?p=2155","title":{"rendered":"MySQL example: OUTER JOINs"},"content":{"rendered":"<p>I often want to know which items in a table are not in another table. In this example, I have two tables of words. The big table has ~68,000 words and the small one has ~29,000. Since they both have the field name, I use an alias in the SELECT statement so that I don\u2019t have to decipher the results. I also put the fields in the same order as the join so that the logic of the query is consistent with the SELECT statement, but you can put them in any order.<\/p>\n<p>Remember that a LEFT OUTER JOIN contains all of the records from the first table. If there is no corresponding record in the second table, the values for its fields are null. <\/p>\n<p><pre><code class=\"preserve-code-formatting\">\nSELECT SmallTable.word AS SmallWord, BigTable.word AS BigWord\nFROM SmallTable\nLEFT OUTER JOIN BigTable \nON&nbsp;&nbsp;SmallTable.word = BigTable.word\nWHERE BigTable.word IS NULL\nORDER BY `SmallTable`.`word` ASC\n<\/code><\/pre><\/p>\n<p>The query yields 364 words and looks like this:<\/p>\n<p><img decoding=\"async\" src=\"\/images\/LEFT_OUTER_JOIN.png\" alt=\"LEFT OUTER JOIN Result\" \/><\/p>\n<p>Just for fun, the reverse query yields 38,776 words and looks like this.<\/p>\n<p><pre><code class=\"preserve-code-formatting\">\nSELECT SmallTable.word AS SmallWord, BigTable.word AS BigWord\nFROM BigTable\nLEFT OUTER JOIN SmallTable \nON SmallTable.word = BigTable.word \nWHERE SmallTable.word IS NULL\nORDER BY `BigTable`.`word` ASC\n<\/code><\/pre><\/p>\n<p><img decoding=\"async\" src=\"\/images\/LEFT_OUTER_JOIN2.png\" alt=\"LEFT OUTER JOIN Result 2\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I often want to know which items in a table are not in another table. In this example, I have two tables of words. The big table has ~68,000 words and the small one has ~29,000. Since they both have the field name, I use an alias in the SELECT statement so that I don\u2019t &hellip; <a href=\"https:\/\/www.wellgolly.com\/?p=2155\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">MySQL example: OUTER JOINs<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-2155","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2155","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2155"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2155\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2155"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2155"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}