שימושים מורכבים ב-Join

אני מניח שכל מי שקורא את הפוסט יודע מה ההבדל בין Inner Join ל-Left Join, והכוונה שלי היא לטפל במקרה יותר מורכב. נניח שיש לנו 3 טבלאות:

1. Customers

2. Orders

3. OrderItems

יש כמובן Foreign Key מהטבלה השלישית לשנייה, ומהשנייה לראשונה.

להלן הקוד ליצירת הטבלאות ולאיכלוסן בנתונים:

Use tempdb;

Go

Drop Table If Exists OrderItems;

Drop Table If Exists Orders;

Drop Table If Exists Customers;

CREATE TABLE Customers (

    CustomerID INT IDENTITY(1,1) NOT NULL,

    FirstName NVARCHAR(100) NOT NULL,

    LastName NVARCHAR(100) NOT NULL,

    Email NVARCHAR(255) NOT NULL,

    RegistrationDate DATETIME2 DEFAULT GETDATE(),

    -- Primary Key constraint

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerID),

    -- Unique constraint to prevent duplicate emails

    CONSTRAINT UQ_Customers_Email UNIQUE (Email)

);

CREATE TABLE Orders (

    OrderID INT IDENTITY(1,1) NOT NULL,

    CustomerID INT NOT NULL,

    OrderDate DATETIME2 DEFAULT GETDATE(),

    TotalAmount DECIMAL(10, 2) NOT NULL,

    ShippingAddress NVARCHAR(500) NULL,

    -- Primary Key constraint

    CONSTRAINT PK_Orders PRIMARY KEY (OrderID),

    -- Foreign Key constraint linking to the Customers table

    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)

        REFERENCES Customers (CustomerID)

        ON DELETE NO ACTION  -- Prevents deleting a customer if they have orders

);

CREATE TABLE OrderItems (

    OrderItemID INT IDENTITY(1,1) NOT NULL,

    OrderID INT NOT NULL,

    ProductID INT NOT NULL,

    Quantity INT NOT NULL,

    UnitPrice DECIMAL(10, 2) NOT NULL,

    -- Primary Key constraint

    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderItemID),

    -- Foreign Key constraint linking to the Orders table

    CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (OrderID)

        REFERENCES Orders (OrderID)

        ON DELETE CASCADE  -- Deletes order items if their parent order is deleted

);

-- Enable manual insertion of ID values

SET IDENTITY_INSERT Customers ON;

GO

INSERT INTO Customers (CustomerID, FirstName, LastName, Email, RegistrationDate) VALUES

(1, 'John', 'Smith', 'john.smith@email.com', '2024-01-15'),

(2, 'Jane', 'Doe', 'jane.doe@email.com', '2024-02-20'),

(3, 'Peter', 'Jones', 'peter.jones@email.com', '2024-03-10'),

(4, 'Alice', 'Brown', 'alice.brown@email.com', '2024-04-05'),

(5, 'Eve', 'Williams', 'eve.williams@email.com', '2024-05-01'); -- This customer will have no orders

-- Disable manual insertion of ID values

SET IDENTITY_INSERT Customers OFF;

GO

-- Enable manual insertion of ID values

SET IDENTITY_INSERT Orders ON;

GO

INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount, ShippingAddress) VALUES

-- Orders for John Smith (CustomerID = 1)

(1, 1, '2025-08-10', 149.98, '123 Oak St'),

(2, 1, '2025-09-01', 29.99, '123 Oak St'),

(3, 1, '2025-09-22', 75.00, '123 Oak St'),

-- Orders for Jane Doe (CustomerID = 2)

(4, 2, '2025-07-18', 19.99, '456 Maple Ave'),

(5, 2, '2025-09-15', 350.50, '456 Maple Ave'),

-- Orders for Peter Jones (CustomerID = 3)

(6, 3, '2025-06-05', 45.75, '789 Pine Ln'),

(7, 3, '2025-08-21', 89.99, '789 Pine Ln'),

(8, 3, '2025-09-03', 120.00, '101 Birch Rd'),

(9, 3, '2025-09-11', 55.49, '101 Birch Rd'),

-- Orders for Alice Brown (CustomerID = 4)

(10, 4, '2025-09-23', 0.00, '101 Birch Rd'); -- This order will have no items

-- Disable manual insertion of ID values

SET IDENTITY_INSERT Orders OFF;

GO

-- Enable manual insertion of ID values

SET IDENTITY_INSERT OrderItems ON;

GO

INSERT INTO OrderItems (OrderItemID, OrderID, ProductID, Quantity, UnitPrice) VALUES

-- Items for Order #1

(1, 1, 101, 1, 99.99),

(2, 1, 205, 2, 24.99),

-- Items for Order #2

(3, 2, 310, 1, 29.99),

-- Items for Order #3

(4, 3, 401, 3, 25.00),

-- Items for Order #4

(5, 4, 101, 1, 19.99),

-- Items for Order #5

(6, 5, 502, 1, 299.00),

(7, 5, 205, 2, 25.75),

-- Items for Order #6

(8, 6, 310, 5, 9.15),

-- Items for Order #7

(9, 7, 620, 1, 89.99),

-- Items for Order #8

(10, 8, 101, 2, 40.00),

(11, 8, 401, 1, 40.00),

-- Items for Order #9

(12, 9, 730, 1, 55.49),

-- Add more items to meet the 20-line requirement

(13, 1, 310, 1, 0.00), -- Free item

(14, 4, 205, 2, 0.00), -- Promo

(15, 5, 730, 1, 0.00), -- Included accessory

(16, 6, 101, 1, 0.00), -- Bonus

(17, 7, 401, 4, 0.00), -- Sample

(18, 8, 502, 1, 0.00), -- Gift

(19, 9, 620, 2, 0.00), -- Giveaway

(20, 2, 401, 1, 0.00); -- Extra part

-- Disable manual insertion of ID values

SET IDENTITY_INSERT OrderItems OFF;

GO

UPDATE o

SET

    o.TotalAmount = ISNULL(oi.CalculatedTotal, 0)

FROM

    Orders AS o

LEFT JOIN (

    SELECT

        OrderID,

        SUM(Quantity * UnitPrice) AS CalculatedTotal

    FROM

        OrderItems

    GROUP BY

        OrderID

) AS oi ON o.OrderID = oi.OrderID;

GO

גילוי נאות: הפוסט הזה הוא שלי, אבל את הקודים הנ"ל יצרתי בעזרת ה-AI (במקרה זה - Gemini), והשארתי את העדויות המרשיעות בפנים (התייחסות של הכלי לפרומט שלי...). חוסך המון עבודה שחורה, עבד תקין מהריצה הראשונה, והכנסתי מספר שינויים מינוריים - לא יותר.

אם ננסה להציג את כל המידע שבטבלאות בפקודה אחת, נקבל משהו כזה:

Select *

From   Customers C

Inner Join Orders O

              On C.CustomerID=O.CustomerID

Inner Join OrderItems I

              On O.OrderID=I.OrderID

Order By C.CustomerID,

              O.OrderID,

              I.OrderItemID;

כבר אפשר לשים לב ששני לקוחות נעלמו: 4 & 5. לקוח 5 נעלם כי אין לו הזמנות ולכן ה-Inner Join פילטר אותו, ואילו ללקוח 4 יש הזמנה (10) אלא שאין לה פריטים.

כדי לא לאבד מידע אפשר להשתמש ב-Left Join בשני המקרים ואזי:

Select *

From   Customers C

Left Join Orders O

              On C.CustomerID=O.CustomerID

Left Join OrderItems I

              On O.OrderID=I.OrderID

Order By C.CustomerID,

              O.OrderID,

              I.OrderItemID;

עכשיו ניתן לראות גם את לקוח 4 עם הזמנה 10 נטולת הפריטים, וגם את לקוח 5 ללא ההזמנות.

עכשיו הגיעה הודעה בהולה מהמכירות שהם רוצים לקבל דוח עם כל הלקוחות (גם אם אין להם הזמנות), אבל להתעלם מהזמנות ללא שורות. כלומר- להציג גם את לקוח 4 וגם את לקוח 5, אבל ללא הזמנות: את לקוח 5 כי אין לו הזמנות, ואת לקוח 4 כי הזמנה ריקה אינה נחשבת להזמנה.

התשובה האינטואיטיבית והשגוייה היא בערך כך:

Select *

From   Customers C

Left Join Orders O

              On C.CustomerID=O.CustomerID

Inner Join OrderItems I

              On O.OrderID=I.OrderID

Order By C.CustomerID,

              O.OrderID,

              I.OrderItemID;

במקרה זה נקבל את אותו פלט כמו בשאילתה הראשונה עם שני ה-Inner Join, כי השני "לא מתחשב" ב-Left Join שלפניו, ומפלטר את כל מי שאין לו הזמנה. מה עושים?

אפשרות אחת היא לשנות את הסדר, קודם כל ה-Inner Join ורק אחרי זה ה-Left Join, וכממתבקש מכך - להשתמש ב-Right Join:

Select C.*,O.*,I.*

From   OrderItems I

Inner Join Orders O

              On O.OrderID=I.OrderID

Right Join Customers C

              On C.CustomerID=O.CustomerID

Order By C.CustomerID,

              O.OrderID,

              I.OrderItemID;

כל הלקוחות בפנים, כולל 4 & 5 שמופיעים ללא הזמנות.

אפשרות שנייה היא להשתמש בסוגריים וליצור Subquery שיתבצע קודם:

Select *

From   Customers C

Left Join (Select O.*,

                           I.OrderItemID,

                           I.ProductID,

                           I.Quantity,

                           I.UnitPrice

              From   Orders O

              Inner Join OrderItems I

                           On O.OrderID=I.OrderID) OI

              On C.CustomerID=OI.CustomerID

Order By C.CustomerID,

              OI.OrderID,

              OI.OrderItemID;

אינני מצרף צילום מסך - הפלט הוא כמו בשאילתה הקודמת.

האפשרות השלישית שהיא הכי אלגנטית ומפתיעה מעט בניסוח שלה:

Select *

From   Customers C

Left Join Orders O

Inner Join OrderItems I

              On O.OrderID=I.OrderID

              On C.CustomerID=O.CustomerID

Order By C.CustomerID,

              O.OrderID,

              I.OrderItemID;

העברנו את פסוקית (clause) ה-On הראשונה לסוף (זו של ה-Left Join) וכך הוא מתבצע לאחר ה-Inner Join !

כפי שאפשר לראות - לאחר ה-Left join אין פסוקית On ולאחר ה-Inner Join יש שתיים.

הפלט כמו בשתי השאילתות הקודמות. לגזור ולשמור!