CODEWITHSUNDEEP
sqlsqlite
User3
User3

Reputation: 2535

How to implement/improve/ make faster this query with joins

Please bear with me I am not skilled in SQL:

I have three tables

1) Notifications - stores all my data

2) GroupTable - Has the names of groups and related id

3) GroupUser - this table maps Uname and Udob to a group from GroupTable.

Now before I fetch records from Notifications I want to check the GroupTable for GroupID take this GroupID and look in GroupUser for all the records in this GroupID (Names,DOB as these are unique) Once I get this data I want to fetch records from Notifications table for the Names and DOB's in ascending order of the date:

So far I have the following query, it works fine just that I am not satisfied and I think this can be improved: 

 SELECT 
    * 
 FROM 
   (SELECT 
        * 
    FROM Notifications 
    WHERE 
          DateToNotify < '2016-03-24' AND 
          NotificationDateFor IN 
                (SELECT gu.Name 
                 FROM GroupUser AS gu 
                 INNER JOIN GroupTable AS gt ON 
                               gu.GroupID = gt._id  AND
                               gt.GroupName = "Groupn"
                ) AND
          DOB IN 
                (SELECT gu.DOB 
                 FROM GroupUser AS gu 
                 INNER JOIN GroupTable AS gt ON 
                               gu.GroupID = gt._id AND 
                               gt.GroupName = "Groupn"
                )
     ) as T 

 ORDER BY 
      SUBSTR(DATE('NOW'), 0) > SUBSTR(DateToNotify, 0)
    , SUBSTR(DateToNotify, 0)

Upvotes: 0

Views: 42

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

I don't think that you would get this faster with joins instead of the IN clauses. It can be that re-writing would not even change the execution plan, because the dbms tries to access the data in the optimal way anyhow.

It seems a bit strange that you don't look for group users matching name and dob, but only ensure that there are group users matching the name and - possibly other - group users matching the dob. But as you say that the query works fine as is, okay.

EDIT: Okay, according to your comment you actually want groupuser matches on both name and dob. So what you are looking for would be

AND (NotificationDateFor, DOB) IN (SELECT gu.Name, gu.DOB FROM ...)

But SQLite doesn't support this beautiful syntax (Oracle is the only dbms I know of that does).

So you either join or use EXISTS.

With JOIN:

select distinct n.*
from notifications n
join
(
  select name, dob 
  from groupuser
  where groupid = (select _id from grouptable where groupname = 'groupn')
) as gu on n.notificationdatefor = gu.name and n.dob = gu.dob
where n.datetonotify < '2016-03-24'
order by date('now') > n.datetonotify, n.datetonotify;

With EXISTS:

select *
from notifications n
where datetonotify < '2016-03-24'
and exists
(
  select *
  from groupuser gu
  where gu.groupid = (select _id from grouptable where groupname = 'groupn')
  and gu.name = n.notificationdatefor
  and gu.dob = n.dob
)
order by date('now') > n.datetonotify, n.datetonotify;

Upvotes: 1

Related Questions