SQL JOIN查找没有特定值的匹配记录的记录 [英] SQL JOIN To Find Records That Don't Have a Matching Record With a Specific Value
问题描述
我正在尝试加快几年前为雇主的购买授权应用程序编写的代码.基本上,我有一个SLOW子查询,我想用JOIN替换(如果更快).
I'm trying to speed up some code that I wrote years ago for my employer's purchase authorization app. Basically I have a SLOW subquery that I'd like to replace with a JOIN (if it's faster).
当主管登录到应用程序时,他会看到他尚未授权或拒绝的购买请求列表.该列表是通过以下查询生成的:
When the director logs into the application he sees a list of purchase requests he has yet to authorize or deny. That list is generated with the following query:
SELECT * FROM SA_ORDER WHERE ORDER_ID NOT IN
(SELECT ORDER_ID FROM SA_SIGNATURES WHERE TYPE = 'administrative director');
sa_order中只有大约900条记录,而sa_signature中只有1800条记录,该查询仍然需要大约5秒钟的时间来执行.我尝试使用LEFT JOIN检索我需要的记录,但是我只能获取sa_order中没有匹配记录的sa_order记录,并且我需要sa_order记录具有没有匹配记录的'行政主管'类型" ".非常感谢您的帮助!
There are only about 900 records in sa_order and 1800 records in sa_signature and this query still takes about 5 seconds to execute. I've tried using a LEFT JOIN to retrieve records I need, but I've only been able to get sa_order records with NO matching records in sa_signature, and I need sa_order records with "no matching records with a type of 'administrative director'". Your help is greatly appreciated!
两个表的架构如下:
涉及的表具有以下布局:
The tables involved have the following layout:
CREATE TABLE sa_order
(
`order_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`order_number` BIGINT NOT NULL,
`submit_date` DATE NOT NULL,
`vendor_id` BIGINT NOT NULL,
`DENIED` BOOLEAN NOT NULL DEFAULT FALSE,
`MEMO` MEDIUMTEXT,
`year_id` BIGINT NOT NULL,
`advisor` VARCHAR(255) NOT NULL,
`deleted` BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE TABLE sa_signature
(
`signature_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`order_id` BIGINT NOT NULL,
`signature` VARCHAR(255) NOT NULL,
`proxy` BOOLEAN NOT NULL DEFAULT FALSE,
`timestamp` TIMESTAMP NOT NULL DEFAULT NOW(),
`username` VARCHAR(255) NOT NULL,
`type` VARCHAR(255) NOT NULL
);
推荐答案
在sa_signatures (type, order_id)
上创建索引.
除非将sa_signatures
允许为空,否则无需将查询转换为LEFT JOIN
.使用索引,NOT IN
也将执行.但是,以防万一您好奇:
This is not necessary to convert the query into a LEFT JOIN
unless sa_signatures
allows nulls in order_id
. With the index, the NOT IN
will perform as well. However, just in case you're curious:
SELECT o.*
FROM sa_order o
LEFT JOIN
sa_signatures s
ON s.order_id = o.order_id
AND s.type = 'administrative director'
WHERE s.type IS NULL
您应该从sa_signatures
中选择一个NOT NULL
列,以使WHERE
子句表现良好.
You should pick a NOT NULL
column from sa_signatures
for the WHERE
clause to perform well.
这篇关于SQL JOIN查找没有特定值的匹配记录的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!