更好的查询两个表上的LEFT JOIN并仅获得匹配的结果

兰萨娜·卡玛拉(Lansana Camara)

我有一个order与两个联接表关联的表:order_buyerorder_seller

表结构如下所示:

CREATE TABLE IF NOT EXISTS `order` (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    -- some fields...

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS order_seller (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id INT(10) UNSIGNED NOT NULL,
    seller_id INT(10) UNSIGNED NOT NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    CONSTRAINT fk_order_seller_order_id FOREIGN KEY (order_id) REFERENCES `order` (id) ON DELETE CASCADE,
    CONSTRAINT fk_order_seller_seller_id FOREIGN KEY (seller_id) REFERENCES user (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS order_buyer (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id INT(10) UNSIGNED NOT NULL,
    buyer_id INT(10) UNSIGNED NOT NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    CONSTRAINT fk_order_buyer_order_id FOREIGN KEY (order_id) REFERENCES `order` (id) ON DELETE CASCADE,
    CONSTRAINT fk_order_buyer_buyer_id FOREIGN KEY (buyer_id) REFERENCES user (id) ON DELETE CASCADE
);

如果我是查看我的订单历史记录的买家,我想获取所有order_buyer.buyer_id等于我的用户ID的订单卖方也有同样的想法。

但是我的查询是这样的,我不知道是查询的是买主还是卖主。我只有一个用户ID。因此,我需要其中一个匹配,如果匹配,那么我想收集该订单。

以下查询不起作用,因为它给了我每笔订单:

SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
    ON order_seller.seller_id = 1
    AND order_seller.order_id = order.id
LEFT JOIN order_buyer
    ON order_buyer.buyer_id = 1
    AND order_buyer.order_id = order.id
WHERE order.status = "PENDING"

下面的查询通过AND从联接中删除and使其成为WHERE IN结果集子句来解决上述问题

SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
    ON order_seller.seller_id = 1
LEFT JOIN order_buyer
    ON order_buyer.buyer_id = 1
WHERE order.status = "PENDING"
    AND order.id IN (order_buyer.order_id, order_seller.order_id)

我的问题是,是否有更好的查询来实现同一目标?这对我来说似乎是肮脏的。感觉不自然。

保罗·斯皮格尔

您可以将两个查询结合起来:

SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
    ON order_seller.seller_id = 1
    AND order_seller.order_id = order.id
LEFT JOIN order_buyer
    ON order_buyer.buyer_id = 1
    AND order_buyer.order_id = order.id
WHERE order.status = "PENDING"
    AND order.id IN (order_buyer.order_id, order_seller.order_id)

它应该比第二个查询快,但返回的结果相同。

您也可以将最后一个条件更改为

COALESCE(order_buyer.order_id, order_seller.order_id) IS NOT NULL

这基本上与Gordon的第一个查询相同。

但是-这里的问题是,引擎需要先读取所有待处理的订单,然后才能通过userID对其进行过滤。使用EXISTS子查询也会遇到同样的问题。

相反,我将使用UNION ALL查询:

SELECT `order`.*
FROM `order`
JOIN order_seller ON order_seller.order_id = order.id
WHERE order.status = "PENDING" AND order_seller.seller_id = 1
UNION ALL
SELECT `order`.*
FROM `order`
JOIN order_buyer ON order_buyer.order_id = order.id
WHERE order.status = "PENDING" AND order_buyer.buyer_id = 1

为了避免过多的代码重复,您可以使用UNION ALL子查询:

SELECT o.*
FROM (
    SELECT order_id FROM order_seller WHERE seller_id = 1
    UNION ALL
    SELECT order_id FROM order_buyer WHERE buyer_id = 1
) x
JOIN `order` o ON o.id = x.order_id
WHERE o.status = "PENDING"

您将至少需要一个索引order(status)由于主键(order.id)是它的隐式部分,因此索引可以同时用于JOIN-ON和WHERE子句。对于其他表,在order_seller(seller_id)上的索引order_buyer(buyer_id)可能很好。但在复合索引order_seller(seller_id, order_id)order_buyer(buyer_id, order_id)效果会更好。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

两个表上的 SQL 子查询或 JOIN,匹配一次删除的值

JPA标准查询-如何在两个表上实现联接以在单个查询中获得所需的结果

MYSQL从两个查询(不是表)中仅选择匹配的记录,然后按B查询对结果进行排序

如何在同一个表上合并两个查询以在MySQL中获得单个结果集

在两个表上都带有where子句的Rails LEFT OUTER JOIN

多个标签搜索查询仅通过1个标签匹配获得结果

加入两个表并获得结果

LEFT JOIN 查询仅返回表 2 中存在的结果

SQL LEFT JOIN两个表而不使用GROUP BY

如何使用一个SQL查询从两个表中获得结果?

合并两个表,并按比例交错两个表的选择查询结果

如何通过比较同一表中的2列仅获得不匹配的结果

如果仅一个匹配内部联接如何获得结果

从两个具有空LEFT JOIN的表中选择的SQLAlchemy返回空结果

PLSQL过程查询两个表并遍历结果

合并两个表的结果的 SQL 查询

SQL查询合并两个表的结果

加入两个表后立即查询结果

根据没有连接的两个表查询结果

如何创建显示组合两个表的结果的查询

对两个表进行匹配的SQL查询速度更快

与两个表的价格匹配的Postgres查询问题

SQL 查询:从两个表中检索匹配的记录

查询从两个表中选择不匹配的记录

使用JOIN从两个表中选择的SQL查询

如果两个表都有行,MySQL查询仅返回结果

跨两个表的SQL查询仅显示每个标记地址的最新更新结果

SQL查询以正确的顺序比较两个表并输出结果到两个报表

如何在MySQL的同一张表上使用join合并这两个查询?