As mentioned earlier, I’m re-reading the Visual QuickStart Guide to SQL and working out some examples. I’m using phpMyAdmin, so the examples here have backticks. I’m using two tables. The first is a table of words and definitions. The second is examples of word usage from the internet.
In this query, I’m looking for the words that have the most usage citations. It turns out that the top three are egregious with 21 citations, interlocutor with 15, and trepidation with 15.
SELECT `words`.`word`, COUNT(*)
FROM `words`
INNER JOIN `usage`
ON `words`.`word` = `usage`.`word`
GROUP BY `words`.`word`
ORDER BY COUNT(*) DESC
LIMIT 20
Find all the words that are not in the definition portion – mostly alternate spellings and plurals. This table has the number of rows that are in the usage table.
SELECT `words`.`word`, `usage`.`word`
FROM `words`
RIGHT OUTER JOIN `usage`
ON `words`.`word` = `usage`.`word`
ORDER BY `words`.`word` ASC
After I cleaned up the tables so that every word has at least one usage example and every example is associated with a word I ran the counts again. Just for fun I ran a RIGHT OUTER JOIN or a LEFT OUTER JOIN instead of an INNER JOIN. As you can probably guess, I got the same result with all three methods.
You can also use WHERE syntax and in this case it yields the same result as well.
SELECT `words`.`word`, `usage`.`word`
FROM `words`,`usage`
WHERE `words`.`word` = `usage`.`word`
GROUP BY `words`.`word`
Simulate a FULL OUTER JOIN. In this case, since we cleaned up the database in the previous step, it has the same number of rows as words.
SELECT `words`.`word`, `usage`.`word`
FROM `words`
LEFT OUTER JOIN `usage`
ON `words`.`word` = `usage`.`word`
UNION
SELECT `words`.`word`, `usage`.`word`
FROM `words`
RIGHT OUTER JOIN `usage`
ON `words`.`word` = `usage`.`word`
BIG difference between UNION and UNION ALL. UNION ALL doesn’t remove duplicates.
While I was cleaning up tables, I looked at my products and product details tables to see if there were any orphaned product details. This query finds the rows in product_details that are not in product:
SELECT `product`.`name`, `product_details`.`product_id`
FROM `product`
RIGHT OUTER JOIN `product_details`
ON `product`.`id` = `product_details`.`product_id`
I already know that there are no products without details, otherwise the website would be broken. But this query checks for that.
SELECT `product`.`name`, `product_details`.`product_id`
FROM `product`
LEFT OUTER JOIN `product_details`
ON `product`.`id` = `product_details`.`product_id`