NOT IN子查询改写成LEFT JOIN查询


1.单表NOT IN

订货单表结构

          
CREATE TABLE `customer_order` (
  `customer_order_id` int(11) NOT NULL,
  `customer_order_no` varchar(32) NOT NULL,
  `create_time` datetime(6) NOT NULL
) ENGINE=InnoDB
          
        

订货单表数据

          
+-------------------+-------------------+----------------------------+
| customer_order_id | customer_order_no | create_time                |
+-------------------+-------------------+----------------------------+
|              1001 | DH-20231031-0001  | 2023-10-31 15:44:33.000000 |
|              1002 | DH-20231031-0002  | 2023-10-31 15:44:48.000000 |
|              1003 | DH-20231031-0003  | 2023-10-31 15:44:59.000000 |
|              1004 | DH-20231031-0004  | 2023-10-31 15:45:13.000000 |
|              1005 | DH-20231031-0005  | 2023-10-31 15:45:20.000000 |
|              1006 | DH-20231031-0006  | 2023-10-31 15:45:27.000000 |
|              1007 | DH-20231031-0007  | 2023-10-31 15:45:33.000000 |
|              1008 | DH-20231031-0008  | 2023-10-31 15:45:38.000000 |
|              1009 | DH-20231031-0009  | 2023-10-31 15:45:44.000000 |
|              1010 | DH-20231031-0010  | 2023-10-31 15:45:51.000000 |
+-------------------+-------------------+----------------------------+
          
        

SQL语句

          
SELECT  i1.*
FROM customer_order i1
LEFT JOIN
(
    SELECT  customer_order_id
    FROM customer_order
    WHERE create_time < '2023-10-31 15:45:27'
    AND create_time > '2023-10-31 15:45:13'
) AS i2 ON i1.customer_order_id = i2.customer_order_id
WHERE i2.customer_order_id IS NULL
          
        

2.主从表NOT IN

本质与单表 NOT IN 一样

发货单表结构

          
CREATE TABLE `send_order` (
  `send_order_id` int(11) NOT NULL,
  `customer_order_id` int(11) NOT NULL,
  `send_order_no` varchar(32) NOT NULL,
  `create_time` datetime(6) NOT NULL
) ENGINE=InnoDB
          
        

发货单表数据

          
---------------+-------------------+------------------+----------------------------+
| send_order_id | customer_order_id | send_order_no    | create_time                |
+---------------+-------------------+------------------+----------------------------+
|          2001 |              1003 | FH-20231102-0001 | 2023-10-31 15:48:54.000000 |
|          2002 |              1005 | FH-20231102-0002 | 2023-10-31 15:49:10.000000 |
+---------------+-------------------+------------------+----------------------------+
          
        

SQL语句

          
SELECT  i1.*
FROM customer_order i1
LEFT JOIN
(
    SELECT  customer_order_id
    FROM send_order
    WHERE create_time > '1990-10-31 15:45:27'
) AS i2 ON i1.customer_order_id = i2.customer_order_id
WHERE i2.customer_order_id IS NULL