2

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
barcaman
  • 107
  • 11

2 Answers2

3

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you very much. – barcaman Dec 02 '20 at 22:44
  • quick question, Am I doing okay with creating indexing on foreign keys? The goal is to make the query perform better on large datasets. Let's say I have 500 million unique bugs across 200.000 of softwares? Is using "Indexing" a good solution here? – barcaman Dec 02 '20 at 22:46
  • Okay, so do i need to add them using Include (url) and include (title) correct? – barcaman Dec 02 '20 at 22:50
2

WHERE is used to filter records before any groupings take place. HAVING is used to filter values after they have been groups. Only columns or expressions in the group can be included in the HAVING clause's

charithsuminda
  • 341
  • 2
  • 9