inner join with empty result from right table

Question

I have 2 tables, restaurants and orders, each restaurant can have many orders

restaurants table
id
name


orders table
id
restaurant_id
date

I need to find the restaurants that have no orders on some date range. In orders table I save the order dates like - each row represents one day. So, I need to make inner join, but with no results from the orders table. Say, I need to find restaurants that are free from 2013-08-09 to 2013-08-11 date range. How can I achieve this ? How to make a query, that will give the restaurants with no matching in the orders table - according to the date range ?

Actually I can do it saving all the dates in the orders table with status not_ordered, and make inner join with not_ordered = true condition, but in that case I will have to populate all the table with the dates, which is not a good thing in my case.

Thanks

look here also, the similar problem, but without accent on joins — rook
– rook, Commented Aug 8, 2013 at 21:54

Wirus · Accepted Answer · 2013-08-08 21:18:02Z

9

select r.*
from restaurant r
left join orders o on r.id = o.restaurant_id and o.date between '...' and '...'
where o.id is null;

Or you can do it using not exists as shown in other answers.

edited Aug 8, 2013 at 21:18

answered Aug 8, 2013 at 21:11

Wirus

1,19010 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

spencer7593 Over a year ago

+1. This is the classic "anti-join" pattern: a LEFT JOIN to find matching rows (all restaurants along with matching orders", and then a WHERE clause to eliminate restaurants that had a matching rows in orders. This is usually more efficient than an equivalent query using a NOT IN or NOT EXISTS predicate.

Ike Walker · Accepted Answer · 2013-08-08 21:12:00Z

3

You don't want to use an inner join for this. You can do it with an outer join, or with NOT EXISTS and a sub-query.

Here's an example of the latter approach:

select r.id,r.name 
from restaurants r 
where not exists (
  select NULL
  from orders o 
  where o.restaurant_id = r.id 
  and o.date >= '2013-08-09'
  and o.date <= '2013-08-11'
);

answered Aug 8, 2013 at 21:12

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

3 Comments

spencer7593 Over a year ago

+1. the correlated subquery has a reference to an expression from the outer query. this is how it is done, though we usually (not always) get better performance with an anti-join.

Ike Walker Over a year ago

I use the anti-join more often myself, but I think it's useful to know both approaches.

spencer7593 Over a year ago

I agree it's useful to know both approaches, which is why I upvoted your answer. Sometimes the NOT EXISTS predicate is the more appropriate approach.

Michael Goldshteyn · Accepted Answer · 2013-08-08 21:17:57Z

0

I don't know mysql very well, but this should work as general SQL:

SELECT * 
FROM restaurants 
WHERE NOT EXISTS(SELECT 1 
                 FROM order 
                 WHERE restaurant_id=id AND 
                       date BETWEEN '2013-08-09' AND '2013-08-11')

edited Aug 8, 2013 at 21:17

answered Aug 8, 2013 at 21:12

Michael Goldshteyn

74.9k25 gold badges136 silver badges183 bronze badges

1 Comment

spencer7593 Over a year ago

Is id in the subquery a reference to the id column in the restaurant table or the order table? Best practice dictates that column references be qualified with either a table name, or preferably, a table alias.

Collectives™ on Stack Overflow

inner join with empty result from right table

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related