Use HAVING or WHERE?

Question

I am confused about when to use HAVING and when to use WHERE. I need to

Find all of the bugs on software Debugger that pertain to the /main.html

This is my query

select Tickets.TicketID, b.Data
from Bugs b
Inner Join Tickets
On b.TicketID = Tickets.TicketID
Inner Join Softwares
on Software.SoftwareId = Tickets.SoftwareID
where Software.URL = 'http://debugger.com' and Tickets.Title = '/main.html'

NOTE: THIS GIVES ME DESIRED RESULT But I want to make sure I am not missing anything important here. Maybe should I use HAVING somewhere here?

Also in order to make the query perform better on a large dataset, I have created an index on foreign keys

create nonclustered index IX_Tickets_SoftwareId
on [dbo].[Tickets] ([SoftwareId])
go
create nonclustered index IX_Bugs_TicketsId
on [dbo].[Bugs] ([TicketsId])

Am doing allright?

Answer 1

Your query is fine. You want to filter individual records, which is what the WHERE clause does.

The HAVING clause comes into play in aggregate queries - queries that use GROUP BY, and its purpose is to filter groups of records, using aggregate functions (such as SUM(), MAX() or the-like). It makes no sense for your query, that does not use aggregation.

Incidently, I note that your are not returning anything from the softwares table, so that join is used for filtering only. In such situation, I find that exists is more appropriate, because it is explicit about its purpose:

select t.ticketid, b.data
from bugs b
inner join tickets t on b.ticketid = t.ticketid
where t.title = '/main.html' and exists (
    select 1 
    from softwares s
    where s.softwareid = t.softwareid and s.url = 'http://debugger.com'
)

For performance, consider an index on softwares(softwareid, url), so the subquery execute efficiently. An index on tickets(ticketid, title) might also help.