The Intuitive Way to Understand the Zig-Zag JOINing with IIF()


In a real world scenario, we can sale in our online store on standard or discounted prices.

In this exercise we use three tables to demonstrate the zig-zag JOIN:

Create database

Create test tables

Sales

Data (or Fact) table that stores information for customer, item and quantities. Column IsItemDiscounted is the tricky one. It is used to determine how we route the link between Sales and Items tables.

Sales

Sales

ItemsStandardDiscounted

Links ItemID (the standard item – regular price and item attributes) to DiscountedItemID (discounted item – different price and item attributes)

ItemsStandardDiscounted

ItemsStandardDiscounted

Items

In this table we store the standard and discounted items on separate rows. This gives us the chance to treat them separately.

Items

Items

The DML Query Statement that is using zig-zag JOINing:

The logic of this statement:

  1. Column IsItemDiscounted in Sales table shows what type of item is sold – standard or discounted
  2. Table ItemsStandardDiscounted routes (uses as a link column to table Items) to the standard or discounted item
  3. Table Items is linked to table ItemsStandardDiscounted, based on IsItemDiscounted in Sales table

The result:

Result

Translation:

  • On the first row ItemID = 11 is linked to ItemID = 11. Hense IsItemDiscounted is 1, DiscountedItemID = 111 is linked to ItemID = 111 and the price of ItemID = 111 (4.99) is used in the calculation (52 * 4.99 = 259.48)
  • On the last row ItemID = 11 is linked to ItemID = 11. Hense IsItemDiscounted is 0, ItemID = 11 is linked to ItemID = 11 and the price of ItemID = 11 (5.23) is used in the calculation (1 * 5.23 = 5.23)

Are you new in SQL Server?
Do you struggle with your SQL understanding? I can’t blame you. The SQL books these days are so boring and hard to follow.
My brand new book Learn SQL Server Intuitively. Transact-SQL: The Solid Basics changes everything.

Keep it simple :-)

 

Leave a comment

Your email address will not be published. Required fields are marked *