\nSELECT * FROM `customers` \nWHERE state IN (SELECT state FROM ASHA WHERE state LIKE 'C%')\n<\/code><\/pre>
\nThis is actually a very inefficient query, taking 24.1081 seconds. The normal way to do this query,
\n\nSELECT * FROM `customers`\nWHERE state LIKE 'C%')\n<\/code><\/pre>
\ntakes 0.0008 seconds. So the first lesson I learned is think about whether a subquery is the best way to do what you want. Even though this is a ‘simple’ query, the subquery runs for each row in the outer query. And the subquery queries each row in the table. So you can see that the number of queries that are run is n2<\/sup>.<\/p>\nHere\u2019s one that makes more sense. Each app belongs to a category. The category table is the same one I used in other posts. Not all categories are being used in apps. But each app has a category. I\u2019d like to know how many apps are in each category.
\n
\nSELECT category.name, categorycount.NumberOfApps \nFROM category \nINNER JOIN\n(SELECT category_id, COUNT(*) AS NumberOfApps FROM apps GROUP BY category_id) categorycount\nON categorycount.category_id = category.id\nORDER BY LOWER(category.name)\n<\/code><\/pre><\/p>\nThis yields the following results in phpMyAdmin:
\n![\"Category](\"\/images\/mySQL_CategoryCount.png\")
<\/p>\n
A couple of interesting things about this query. First note that the subquery is labeled with an alias. And all of the column names are preceded by a table name or alias. If you run the subquery by itself, you get a table that has eight rows in it. The inner join just matches the names of the categories with the category ids from the apps table. Since the subquery is able to be independently run, this is what is called a simple query. The ORDER BY statement uses LOWER to put the categories in strict alphabetical order because one of the categories is lower case and it will appear at the end of the table if you don\u2019t use LOWER.<\/p>\n
In a previous post I used the favorite words database to do an INNER JOIN. Here I use it to count the number of words in each category. Since the tables are set up similarly to the apps, I can make just a slight modification to the query and it works.
\n
\nSELECT words_categories.name, categorycount.NumberOfWords\nFROM words_categories \nINNER JOIN\n(SELECT category_id, COUNT(*) AS NumberOfWords FROM words GROUP BY category_id) categorycount\nON categorycount.category_id = words_categories.id\nORDER BY LOWER(words_categories.name)\n<\/code><\/pre>
\n![\"Word](\"\/images\/mySQL_NumberOfWords.png\")
<\/p>\nIn this case, the subquery makes it pretty clear what you are trying to do. But you can also write this as an INNER JOIN without the subquery.
\n
\nSELECT words_categories.name, COUNT(*) AS NumberOfWords\nFROM words_categories \nINNER JOIN\nwords\nON words.category_id = words_categories.id\nGROUP BY words_categories.name\nORDER BY LOWER(words_categories.name)\n<\/code><\/pre><\/p>\nThe subquery is actually a bit faster, .0002 sec vs .0003 without. This is a small table with around 1200 entries so it doesn\u2019t make any difference.<\/p>\n","protected":false},"excerpt":{"rendered":"
I\u2019m reading about subqueries and thought I\u2019d try a simple one. It doesn\u2019t make sense to actually do it, but it is a good one to start with. Find all of the customer\u2019s whose state starts with a ‘C’. SELECT * FROM `customers` WHERE state IN (SELECT state FROM ASHA WHERE state LIKE 'C%') This … Continue reading mySQL: Subqueries Part 1<\/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-2090","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2090","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=2090"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2090\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}