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.
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