As mentioned earlier, I\u2019ve been brushing up on my JOIN terminology using data in my mySQL databases. The results here probably apply to other SQL DBMSs as well.<\/p>\n
These three joins result in almost the same rows with minor\u2014crucial\u2014differences. The INNER JOIN returns all of the rows in the first table that have a match in the second table. The order in which the tables is specified doesn\u2019t matter. If there is a row in either table that doesn\u2019t have a match (specified by the join condition) it doesn\u2019t appear in the join.<\/p>\n
The LEFT OUTER JOIN returns all of the rows in the first table regardless of whether there is a matching the second table. If there is no match in the second table, the values for those fields are NULL. The RIGHT OUTER JOIN returns all of the rows in the second table regardless of whether there is a matching the first table. If there is no match in the first table, the values for those fields are NULL. As you can see by the specification, the order in which the tables are specified for the outer joins matters.<\/p>\n
In my database the `apps` table has 30 rows. The `category` table has 10 rows. Two of the apps do not have a category assigned to them. Two of the categories have no apps associated with them.<\/p>\n
In my database, this code returns 28 rows\u2014the two apps with no assigned category do not appear.
\n
\nSELECT apps.name, app_category.name \nFROM apps\nINNER JOIN app_category \nON category_id = app_category.id\n<\/code><\/pre><\/p>\nThis code returns 30 rows\u2014the two apps with no assigned category appear in the result and the result and the app_category.name is NULL.
\n
\nSELECT apps.name, app_category.name \nFROM apps\nLEFT OUTER JOIN app_category \nON category_id = app_category.id\n<\/code><\/pre><\/p>\nThis is a bit more complicated. This code returns 30 rows\u2014the two apps with no assigned category do not appear in the result. The two categories that have no matching apps have an app.name of NULL.
\n
\nSELECT apps.name, app_category.name \nFROM apps\nRIGHT OUTER JOIN app_category \nON category_id = app_category.id\n<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"As mentioned earlier, I\u2019ve been brushing up on my JOIN terminology using data in my mySQL databases. The results here probably apply to other SQL DBMSs as well. Differences These three joins result in almost the same rows with minor\u2014crucial\u2014differences. The INNER JOIN returns all of the rows in the first table that have a … Continue reading mySQL: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN<\/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-2068","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2068","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=2068"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2068\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}