内部联接，右表为空结果 [英] inner join with empty result from right table

问题描述

我有2个桌子，餐厅和订单，每个餐厅可以有很多订单

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

restaurants table
id
name


orders table
id
restaurant_id
date

我需要找到在某些日期范围内没有订单的餐馆.在订单表中，我将订单日期保存为-每行代表一天.因此，我需要进行内部联接，但orders表中没有结果.假设，我需要查找日期范围不在2013-08-09 to 2013-08-11的餐厅.我怎样才能做到这一点?如何进行查询，从而使订单表中的餐厅不匹配-根据日期范围?

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 ?

实际上，我可以将状态为not_ordered的订单中的所有日期保存在订单表中，并使用not_ordered = true条件进行内部联接，但是在这种情况下，我将必须用日期填充所有表，而不是就我而言是好事.

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.

谢谢

推荐答案

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;

或者您也可以使用not exists来做到这一点，如其他答案所示.

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

这篇关于内部联接，右表为空结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！