使用联接而不是子选择更改SQL查询 [英] Changing a sql query using join instead of a subselect

问题描述

我遇到以下问题: 因为条目的数量可能很大，所以我想在SQL查询中使用联接而不是子选择.

i've got the following problem: Because the amount of entries could get very big, I would like to use a join instead of a subselect in a sql query.

它涉及以下三个简化表:

It regards the following three simplified tables:

devices:
- id

confirmation_requests:
- id
- filePath

confirmation
- id
- requestId (references confirmation_requests.id)
- deviceId (references devices.id)

目标是获取所有给定设备未确认的确认请求(在确认表中有一个条目).

The target is, to get all confirmation requests, which are not confirmed (with an entry in the confirmation table) by a given device.

我只是使用普通的子查询来找到解决方案，您可以在此处找到一个示例: http://sqlfiddle.com/#!2/13fd3/1

I just come to a solution using an ordinary subquery, an example you find here: http://sqlfiddle.com/#!2/13fd3/1

SELECT * 
FROM confirmation_requests 
WHERE id NOT IN (SELECT confirmation_request_id
                     FROM confirmations
                     WHERE device_id = 1);

谢谢！

推荐答案

您可以对表进行LEFT JOIN匹配device_id = 1的确认，然后排除WHERE子句中的确认:

You can do a LEFT JOIN onto the table for confirmations matching device_id = 1 then exclude those in the WHERE clause:

SELECT cr.* 
FROM confirmation_requests cr
LEFT JOIN confirmations c ON (cr.id = c.confirmation_request_id AND c.device_id = 1)
WHERE c.id IS NULL;

这篇关于使用联接而不是子选择更改SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！