SQL如何通过存储过程校验两表关联ID是否匹配并返回对应记录

原有代码存在的问题 #

• 对table2执行了3次独立的INNER JOIN，会产生笛卡尔积，严重影响查询结果准确性
• 变量赋值与匹配查询逻辑混杂，无法实现「先从table1取参数再匹配table2」的需求
• 字段名引用不统一，table1字段名和赋值语句中的别名对应错误

修正后代码 #

USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[nameSP]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 校验table1与table2字段匹配的存储过程
-- =============================================
ALTER PROCEDURE [dbo].[nameSP]  
    @ScheduleID int
AS
BEGIN   
    SET NOCOUNT ON;

    DECLARE @DeliverTypeID int
    DECLARE @ScheduleTypeID int
    DECLARE @CourseID int

    -- 第一步：从table1取出对应输入ScheduleID的三个字段值
    SELECT 
        @DeliverTypeID = DeliverTypeID,
        @ScheduleTypeID = ScheduleTypeID,
        @CourseID = CourseID
    FROM table1
    WHERE ScheduleID = @ScheduleID

    -- 第二步：用取出的三个值匹配table2的对应字段，输出符合条件的记录
    SELECT *
    FROM table2
    WHERE 
        (CourseID = @CourseID OR CourseID IS NULL)
        AND ScheduleTypeID = @ScheduleTypeID
        AND DeliverTypeID = @DeliverTypeID
END

逻辑说明 #

1. 首先根据输入的唯一操作编码@ScheduleID，从table1中查询得到对应的DeliverTypeID、ScheduleTypeID、CourseID三个参数值
2. 再用这三个参数值到table2中做匹配，匹配规则完全符合要求：
   • DeliverTypeID与ScheduleTypeID要求完全相等
   • CourseID支持相等匹配，也兼容table2中CourseID为NULL的场景

示例匹配测试 #

以提供的最小数据为例：

• 当输入@ScheduleID = 67212时，从table1取到的参数为CourseID=2000、DeliverTypeID=1、ScheduleTypeID=1，到table2中无匹配记录
• 当输入@ScheduleID = 70124时，从table1取到的参数为CourseID=5000、DeliverTypeID=4、ScheduleTypeID=4，到table2中无匹配记录

如果匹配规则不需要兼容table2.CourseID为NULL的场景，直接删掉WHERE条件里的OR CourseID IS NULL即可。

内容的提问来源于stack exchange，提问作者Kiter