CODEWITHSUNDEEP
sqlsqliteleft-join
Fizi
Fizi

Reputation: 1851

Perform SQL query and then join

Lets say I have two tables:

Owing to really large tables I would like to first filter the tickets table by date

SELECT id FROM ticket WHERE date >= 'some date'

then I would like to do a left join with the user table. Is there a way to do it. I tried the follwoing but it doesnt work.

 select ticket.id, user.name from ticket where ticket.date >= '2015-05-18' left join user on ticket.userid=user.id;

Apologies if its a stupid question. I have searched on google but most answers involve subqueries after the join instead of what I want which is to perfrom the query first and then do the join for the items returned

To make things a little more clear, the problem I am facing is that I have large tables and join takes time. I am joining 3 tables and the query takes almost 3 seconds. Whats the best way to reduce time. Instead of joining and then doing the where clause, I figured I should first select a small subset and then join.

Upvotes: 0

Views: 117

Answers (1)

dnoeth
dnoeth

Reputation: 60462

Simply put everything in the right order:

select - from - where - group by - having - order by

select ticket.id, user.name 
from ticket left join user on ticket.user_id=user.id
where ticket.date >= '2015-05-18'

Or put it in a Derived Table:

select ticket.id, user.name 
from
 (
   select * from ticket
   where ticket.date >= '2015-05-18'
 ) as ticket 
left join user on ticket.user_id=user.id

Upvotes: 1

Related Questions