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?

up vote 2 down vote accepted
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.
  • Like is case sensitive. Formatting suggestion...capitalize the SQL keywords? You have ASC and DESC caps, but otherwise all lower case. – Michael K Jun 28 '11 at 15:13
  • @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. – Hosam Aly Jun 30 '11 at 7:20
  • As for capitalization, I prefer to use small letters as long as I have syntax highlighting. – Hosam Aly Jun 30 '11 at 7:21
  • 1
    @Hosam Consistency trumps preference for me. I care less about the style than whether it's applied universally :) Good point about other DBMS's. – Michael K Jun 30 '11 at 12:53
  • @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). – Andriy M Jun 30 '11 at 12:57

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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