I've written the following SQL to count the number of times the name 'Cthulhu' turns up for each tag on Stack Overflow (original here):

select t.TagName, count (*) 'Tainted'
from Posts p, Tags t, PostTags pt
where p.Body like '%cthulhu%'
and (pt.PostId = p.Id and t.Id = pt.TagId)
group by t.TagName
order by Tainted DESC, t.TagName ASC

It works, but I'm not used to cutting SQL manually; I'm more accustomed to using ORMs. I tried using CONTAINS instead of LIKE, but apparently Body isn't set up for full-text search.

Could you please provide me some feedback - in particular, are there any best practices I'm missing, and whether there are standard formatting rules for SQL that would make it a bit easier on the eye?


1 Answer 1

select t.TagName, count (*) 'Tainted'
  from Posts p
 inner join PostTags pt on (pt.PostId == p.Id)
 inner join Tags t on (t.Id == pt.TagId)
 where lower(p.Body) like '%cthulhu%'
 group by t.TagName
 order by Tainted desc, t.TagName asc
  • Notice the lower on the body, because like is (should?) be case sensitive.
  • The joins are also easier to read IMHO than the conditions in the where clause.
  • The formatting is based on right-aligning keywords and left-aligning clauses.
  • \$\begingroup\$ Like is case sensitive. Formatting suggestion...capitalize the SQL keywords? You have ASC and DESC caps, but otherwise all lower case. \$\endgroup\$
    – Michael K
    Commented Jun 28, 2011 at 15:13
  • \$\begingroup\$ @Michael K.: Thanks for your notes. I said that like "should" be case sensitive just to be on the safe side (in case a DBMS such as SQL Server or MySQL has an option to make it insensitive). I didn't notice the casing of the words ASC and DESC when I copied them. I have corrected them; thanks. \$\endgroup\$
    – Hosam Aly
    Commented Jun 30, 2011 at 7:20
  • \$\begingroup\$ As for capitalization, I prefer to use small letters as long as I have syntax highlighting. \$\endgroup\$
    – Hosam Aly
    Commented Jun 30, 2011 at 7:21
  • 1
    \$\begingroup\$ @Hosam Consistency trumps preference for me. I care less about the style than whether it's applied universally :) Good point about other DBMS's. \$\endgroup\$
    – Michael K
    Commented Jun 30, 2011 at 12:53
  • \$\begingroup\$ @Michael K: Isn't it case-sensitive simply because the collation for p.Body is case-sensitive? Because this simple test shows that generally speaking, LIKE is not case-sensitive: SELECT * FROM (SELECT 'A' UNION ALL SELECT 'sad' UNION ALL SELECT 'good-bye') x (f) WHERE f LIKE '%A%'. This returns two rows on my computer (SQL Server 2008 R2) as well as in SE Data Explorer (don't know what server they use). \$\endgroup\$
    – Andriy M
    Commented Jun 30, 2011 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.