Well Golly

I’m reading about subqueries and thought I’d try a simple one. It doesn’t make sense to actually do it, but it is a good one to start with. Find all of the customer’s whose state starts with a ‘C’.

SELECT * FROM `customers` 
WHERE state IN (SELECT state FROM ASHA WHERE state LIKE 'C%')

This is actually a very inefficient query, taking 24.1081 seconds. The normal way to do this query,

SELECT * FROM `customers`
WHERE state LIKE 'C%')

takes 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 n².

Here’s 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’d like to know how many apps are in each category.

SELECT category.name, categorycount.NumberOfApps 
FROM category 
INNER JOIN
(SELECT category_id, COUNT(*) AS NumberOfApps FROM apps GROUP BY category_id) categorycount
ON categorycount.category_id =  category.id
ORDER BY LOWER(category.name)

This yields the following results in phpMyAdmin:

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’t use LOWER.

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.

SELECT words_categories.name, categorycount.NumberOfWords
FROM words_categories 
INNER JOIN
(SELECT category_id, COUNT(*) AS NumberOfWords FROM words GROUP BY category_id) categorycount
ON categorycount.category_id =  words_categories.id
ORDER BY LOWER(words_categories.name)

In 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.

SELECT words_categories.name, COUNT(*) AS NumberOfWords
FROM words_categories 
INNER JOIN
words
ON words.category_id =  words_categories.id
GROUP BY words_categories.name
ORDER BY LOWER(words_categories.name)

The subquery is actually a bit faster, .0002 sec vs .0003 without. This is a small table with around 1200 entries so it doesn’t make any difference.

I wanted to find all of the emails in my customer database that were duplicates. This could be because several people at the same business share an address or because someone is in the database twice with slightly different spellings of their name e.g. Kathy and Kathleen, or with and without a middle initial, etc.

Before I started I set all of the email addresses that were empty strings to NULL, since joins don’t match on NULL but do on empty strings.

Then on the first try I used this:

SELECT t1.name, t1.email, t2.name, t2.email
FROM customers AS t1
INNER JOIN customers AS t2 
ON t1.email = t2.email
WHERE t1.name <> t2.name

Or in WHERE notation.

SELECT t1.name, t1.email, t2.name, t2.email
FROM customers AS t1, customers AS t2 
WHERE t1.email = t2.email
AND t1.name <> t2.name

I got 384 records, but I got a lot of duplicates where t1.name and t2.name are reversed. i.e.

Helen Jaffa     hjaffa@ino.net   Helen A Jaffa   hjaffa@ino.net
Helen A Jaffa   hjaffa@ino.net   Helen Jaffa     hjaffa@ino.net

To eliminate duplicates I used a UNION and two OUTER JOINS

SELECT t1.email, t1.name
FROM customers AS t1
LEFT OUTER JOIN customers AS t2 ON t1.email = t2.email
WHERE t1.name <> t2.name
UNION
SELECT t2.email, t2.name
FROM customers AS t2
LEFT OUTER JOIN customers AS t1 ON t1.email = t2.email
WHERE t1.name <> t2.name

This yielded 343 records and no duplicates.

Looking through my tables, I don’t have any where a self-join makes any sense. I do have one table where it will work. In the Minimal Pairs game, each word is associated with another word. A pair might be “stop – top” or “rake – wake”. We have a picture for each word and by doing a self-join, we can see which pictures are duplicated.

SELECT * 
FROM Pairs AS t1 
INNER JOIN Pairs AS t2 
ON t1.word1 = t2.word2

The result shows that two images, key and car, are used twice.

If you like WHERE syntax, this is it:

SELECT * 
FROM Pairs AS t1, Pairs AS t2 
WHERE t1.word1 = t2.word2

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`

Case sensitive selection

These three methods are equivalent.

SELECT name FROM `customers` WHERE CAST(name AS BINARY) LIKE '%Net%'

SELECT name FROM `customers` WHERE name COLLATE utf8_bin LIKE '%Net%'

SELECT name FROM `customers` WHERE name LIKE BINARY '%Net%'

It also works for non-latin characters. This search will find only names with an “ñ” in the name. If you leave out the BINARY you will get all the names with an “n” in the name.

SELECT name FROM `customers` WHERE name LIKE BINARY '%Muñoz%'

BETWEEN

You can search fields containing numbers or characters using BETWEEN.

SELECT * FROM `customers` WHERE id BETWEEN 900 AND 999

You can also search a character string of numbers with BETWEEN.

SELECT * FROM `customers` WHERE zip BETWEEN 90000 AND 99999
SELECT * FROM `customers` WHERE zip*2 BETWEEN 90000 AND 99999

These also work:

SELECT * FROM `customers` WHERE name BETWEEN 'Ja' AND 'Jz'
SELECT * FROM `customers` WHERE name BETWEEN 'Jam' AND 'Jaz'
SELECT * FROM `customers` WHERE city BETWEEN 'A' AND 'M'

You might expect this to work, but it doesn’t. The first set of characters must be alphabetically before the second set of characters.

SELECT * FROM `customers` WHERE name BETWEEN 'Ja' AND 'Hz'

IN

This could come in handy if you want to restrict searches to just a few of the possible values in a field.

SELECT * FROM `customers` WHERE city IN ('Tulsa', 'Orlando')

Derived Columns: CONCAT

mySQL uses the CONCAT function not the concat operator, ||, for concatenation.

SELECT CONCAT(city, ', ', state) AS City_State FROM `customers`

This displays the column heading for the result as City_State, but it is not available for use in a WHERE clause. This fails with an unknown column error:

SELECT CONCAT(city, ', ', state) AS City_State 
FROM `customers` 
WHERE City_State = 'Anchorage, AK'

However, you can wrap the query as follows,

SELECT *
FROM (SELECT CONCAT(city, ', ', state) AS City_State 
FROM `customers`) 
AS firstQuery
WHERE City_State = 'Anchorage, AK'

This doesn’t work in phpMyAdmin or mySQL but works in other DBMSs:

SELECT * FROM `customers`
WHERE city || ', ' || state = 'Anchorage' || ', ' || 'AK'

Substring gotcha

If you want to find the cities that start with ‘Los’, you might be tempted to write the SUBSTRING as SUBSTRING(city, 0, 2) like any normal string access e.g. PHP. But MySQL starts with 1 and then has the number of characters you want to extract. So you’d do it like this:

SELECT city FROM `customers` WHERE SUBSTRING(city FROM 1 FOR 3) LIKE 'Los'

UPPER and LOWER

You can transform fields or literals in SELECT, WHERE, and ORDER BY clauses.

Since all of the state abbreviations are in upper case, this works:

SELECT state FROM `customers` WHERE state COLLATE utf8_bin LIKE UPPER('%ca%')

as does this:

SELECT state FROM `customers` WHERE LOWER(state) COLLATE utf8_bin LIKE'%ca%'

Combining operators

You might want to uppercase the address for mailing labels, this is one way to do it.

SELECT UPPER(CONCAT(city, ', ', state, ' ', zip)) AS City_State_Zip FROM `customers`

The result is

ST. ALBERT, AB T8N 2N9
ANCHORAGE, AK 99503

Interesting Query

Try this query:

SELECT CURRENT_DATE AS  'Date', CURRENT_TIME AS  'Time',  'Well Golly' AS 'Site'

It yields one row:

Date        Time      Site
2014-12-05  13:19:38  Well Golly

Then try this one:

SELECT CURRENT_DATE AS 'Date', CURRENT_TIME AS 'Time', 'Well Golly' AS 'Site', name FROM `customers`

It yields one row for each row in the database:

Date        Time      Site       name
2014-12-05  13:31:59  Well Golly Carmen Edsel
2014-12-05  13:31:59  Well Golly Jitka Dragonola
2014-12-05  13:31:59  Well Golly Deryk Bealer

GROUP BY and COUNT(*)

When testing this one out, I found some values that shouldn’t be in the state field.

SELECT state, COUNT(*) FROM `customers` GROUP BY state 

state   COUNT(*)
D       1
da      1

If you only want selected states, you can use the IN selection criterion:

SELECT state, COUNT(*) 
FROM `customers` 
WHERE state IN ('CA', 'IN') 
GROUP BY state 

Or if you want to find the number of customers in the top 20 cities, ordered from most to least.

SELECT city, state, COUNT(*) as count 
FROM `customers` 
GROUP BY city, state 
ORDER BY count DESC 
LIMIT 20

Note that the SELECT and GROUP BY must have the same fields or you will get incorrect results. In this case, leaving out the state in the group by yields the most customers in Brooklyn, FL

HAVING

Use the HAVING search condition to filter the results.

SELECT city, state, COUNT(*) as count 
FROM `customers` 
GROUP BY city, state 
HAVING COUNT(*) > 50 
ORDER BY count DESC

HAVING is applied after GROUP BY. Its main use is to filter on aggregates—SUM, COUNT, AVG, etc.

In general, you can’t reference aliases in WHERE or GROUP BY clauses, but MySQL allows referencing SELECT level aliases in GROUP BY, ORDER BY and HAVING.