How to simplify embedded mysql query into a JOIN?

I recently read something about performance problems with embedded mysql queries, so I wanted to know how I could change the following to a "JOIN" (supposedly with better performance?).

I have two tables:

CREATE TABLE IF NOT EXISTS `blog_categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `category_name_url` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `category_status` enum('online','offline') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'offline'
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;


CREATE TABLE IF NOT EXISTS `blog_articles` (
  `article_id` int(11) NOT NULL AUTO_INCREMENT,
  `article_title` tinytext COLLATE utf8_unicode_ci NOT NULL,
  `category_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=26 ;

The logic is basically to select all the categories that have articles associated with them. Each row in the blog_articles table includes a category_name

And here is the query I'm using (which checks out and works fine):

$sql = "SELECT category_name
     , category_name_url 
   FROM blog_categories 
   WHERE (
     (SELECT COUNT(*) 
      FROM blog_articles, blog_categories 
      WHERE blog_articles.category_name = blog_categories.category_name
     ) > 0 
     AND blog_categorie开发者_StackOverflows.category_status = 'online')";

I'm still new to "JOIN", and am not sure how to change it when using "COUNT(*)" in the mix too.

Rather than COUNT(*) > 0, use EXISTS

"SELECT category_name, category_name_url FROM blog_categories WHERE EXISTS (" . 
              "(SELECT 1 FROM blog_articles INNER JOIN blog_categories 
                ON blog_articles.category_name = blog_categories.category_name)  AND " . 
              "blog_categories.category_status = 'online'" . 
              ")";

This is the old style join syntax:

SELECT 1 FROM blog_articles, blog_categories 
WHERE blog_articles.category_name = blog_categories.category_name

This is the ANSI form of the same:

SELECT 1 
FROM blog_articles
INNER JOIN blog_categories 
    ON blog_articles.category_name = blog_categories.category_name

Update (in response to posters comments): This query does what you require:

"SELECT category_name, category_name_url 
 FROM blog_categories 
 WHERE category_name IN (SELECT DISTINCT category_name FROM blog_articles)
 AND blog_categories.category_status = 'online'"

I'd use EXISTS:

$sql = "SELECT category_name
             , category_name_url 
        FROM blog_categories 
        WHERE EXISTS
          ( SELECT * 
            FROM blog_articles 
            WHERE blog_articles.category_name = blog_categories.category_name
          ) 
        AND blog_categories.category_status = 'online'
       ";

or JOIN:

$sql = "SELECT c.category_name
             , c.category_name_url 
        FROM blog_categories AS c 
          JOIN blog_articles AS a
            ON a.category_name = c.category_name
        WHERE c.category_status = 'online'
        GROUP BY c.category_name
       ";

@Jay: I doubt your query as it is, shows only categories that have articles posted. It counts all articles from all categories and then either shows all categories (if the count is > 0) or no categories at all (if the count = 0).

The reason is that the blog_categories in your subquery is not related to the blog_categories in the main query by any condition.

You can try this also:

SELECT category_name, category_name_url
FROM blog_categories
WHERE category_id IN (
    SELECT blog_categories.category_id
    FROM blog_articles
    INNER JOIN blog_categories
        ON blog_categories.category_name = blog_articles.category_name
        AND blog_categories.category_status = 'online'
)