Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools Database Foundations SQL Calculating, Aggregating and Other Functions String Functions

Posted by John Magee
John Magee
Courses Plus Student 9,058 Points

Improper MySQL Syntax used in this Video - String Functions - Database

In the String Functions Database video - the lecturer uses the 'having by' clause on the length(username) function, and while it works this is BAD SQL - MySQL should NOT allow this to work.

HAVING BY is a term that is supposed to be used ONLY after you use a GROUP BY

The correct format would have been to say WHERE length(username) < 19

It doesn't matter that it works in MySQL - it's bad practices

ericb2
ericb2
11,429 Points

In the video, Andrew Chalkley presents the following example:

SELECT first_name,
UPPER(last_name), 
LOWER(email), 
LENGTH(username) AS username_length
FROM users HAVING username_length < 19;

None of the functions used are listed as aggregate functions that would make a GROUP BY clause implicit, so it seems like WHERE rather than HAVING would be the appropriate keyword to use, but execution fails in mySQL if you switch the keywords. However, you can use a WHERE statement if it doesn't contain the username_length alias: WHERE LENGTH(username) < 19.

Part of the explanation is in the mySQL documentation: "In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the HAVING clause that are not named in the GROUP BY clause. A MySQL extension permits references to such columns to simplify calculations.". If you turn off this extension (it's on by default), Andrew's code above fails.

The mystery that remains in my mind is how/why mySQL allows the use of the HAVING clause without implicit (i.e. aggregate functions) or explicit GROUP BY.

From a practical standpoint, (1) Andrew HAD to use HAVING instead of WHERE if he wanted to include his alias, and (2) as can be inferred by your remarks, no one using this syntax should expect it to work on other flavors of SQL.

Bruno Calheira , I am tagging you on this comment because it touches on your question, "When should I use HAVING in SQL statements?"

(now that I have made all these pronouncements so definitively, I hope someone will please point out where I've gone wrong! :-) )