Aman Singh
Aman Singh

Reputation: 753

Is it better practice to use subquery in database?

Consider two tables with following schemas

Raipur_Restaurant_List

RestID
name

where RestID is the primary key

Raipur_Restaurant_List_Dine_Types

RestID
Dine_Name

where RestID is the foreign key referenced to Raipur_Restaurant_List table

select distinct RRL.RestID, RRL.name 
from Raipur_Restaurant_List as RRL 
join Raipur_Restaurant_List_Dine_Types as RRLDT 
on RRL.RestID = RRLDT.RestID 
where RRLDT.Dine_Name  = 'HomeDelivery' 
and RRL.RestID 
IN (select RestID from Raipur_Restaurant_List_Dine_Types where Dine_Name  ='Bakeries')

I am using above query to find those restaurants which have both HomeDelivery and Bakeries, Is there any better or efficient way to achieve this task ??

Thanks in advance

Upvotes: 1

Views: 26

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

Another way to accomplish this using just the one join and no subquery instead uses the IN () to match both desired values, but also implements an aggregate COUNT() and limits the result set to those aggregate groups with COUNT() = 2, since that implies that it must have both values:

SELECT DISTINCT
  RRL.RestID,
  RRL.name
FROM
  Raipur_Restaurant_List as RRL 
  JOIN Raipur_Restaurant_List_Dine_Types as RRLDT 
    on RRL.RestID = RRLDT.RestID 
WHERE 
  -- Filter for both values
  RRLDT.Dine_Name IN ('HomeDelivery', 'Bakeries')
-- GROUP BY is needed to apply the COUNT()
GROUP BY
  RRL.RestID,
  RRL.name
-- And filter the aggregate groups 
-- for those having exactly two, meaning
-- both conditions were matched by the IN ()
HAVING COUNT(DISTINCT RRLDT.Dine_Name) = 2

The IN() clause itself would return rows having only one or the other of HomeDelivery,Bakeries in addition to both. By applying the COUNT(), you ensure that only those which match both are returned.

If you need to add additional matches, add them to the IN () list, and also increment the number to compare in the HAVING clause to be the same as the length of the IN () list.

The DISTINCT keyword inside COUNT() is only needed if it were possible for the same Dine_Name to exist more than once per RestID. If there will never be duplicate RestID, Dine_Name pairs in RRLDT, the DISTINCT isn't needed there.

Here is a demonstration...

Upvotes: 2

Related Questions