开发者

How to replace NOT EXISTS nested select with JOIN

开发者 https://www.devze.com 2023-01-17 07:13 出处:网络
Hey there, I\'m kinda stuck trying to optimise a query which has a NOT EXISTS clause on a nested SELECT. I\'ve been rewriting my queries containing nested selects, changing them to joins, but on this

Hey there, I'm kinda stuck trying to optimise a query which has a NOT EXISTS clause on a nested SELECT. I've been rewriting my queries containing nested selects, changing them to joins, but on this occasion I'm not sure how to combine that with the NOT EXISTS clause. I have the following query:

SELECT `reg_no`, COUNT(*) AS `records_found` 
FROM (`club_records` AS `cr`) 
WHERE NOT EXISTS ( 
  SELECT `number` FROM `members` WHERE `me开发者_开发百科mbers`.`number` = `cr`.`alt_reg_no` 
)


http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

SELECT `reg_no`, COUNT(*) AS `records_found`,members`.`number`
FROM `club_records` AS `cr`
LEFT OUTER JOIN members`
ON  `members`.`number` = `cr`.`alt_reg_no
WHERE members`.`number` IS NULL;

attention: member number definition must include NOT NULL constraint

0

精彩评论

暂无评论...
验证码 换一张
取 消