如何使用SQL自连接查询芝加哥在售但迈阿密未售的产品

Got it, let's break down how to solve this problem. You need to identify products that are actively on sale in Chicago, but either aren't available in Miami at all or aren't marked as on sale there. Here are two reliable approaches using SQL, including a self-join method as you mentioned:

Method 1: Using LEFT JOIN (Self-Join) #

This approach joins the table to itself to compare Chicago and Miami records directly:

SELECT DISTINCT c.ProductID, c.Product_Type
FROM your_product_table c
LEFT JOIN your_product_table m
  ON c.ProductID = m.ProductID
  AND UPPER(m.Stock_location) = 'MIAMI'
  AND UPPER(m.On_Sale) = 'Y'
WHERE UPPER(c.Stock_location) = 'CHICAGO'
  AND UPPER(c.On_Sale) = 'Y'
  AND m.ProductID IS NULL;

How this works: #

• We alias the table as c (for Chicago) and m (for Miami) to distinguish the two datasets.
• The LEFT JOIN tries to match each Chicago on-sale product to a corresponding Miami on-sale product.
• If m.ProductID IS NULL, it means either:
  1. The product doesn't exist in Miami's inventory at all, or
  2. The product is in Miami but not marked as on sale.
• We use UPPER() to handle case inconsistencies (like the lowercase 'y' in your sample data for Frozen Food).
• DISTINCT ensures we don't get duplicate product entries, though your sample data looks like each product has one entry per location.

Method 2: Using NOT EXISTS (Subquery) #

This method is often more readable for "exclude matching records" scenarios:

SELECT DISTINCT ProductID, Product_Type
FROM your_product_table
WHERE UPPER(Stock_location) = 'CHICAGO'
  AND UPPER(On_Sale) = 'Y'
  AND NOT EXISTS (
    SELECT 1
    FROM your_product_table
    WHERE ProductID = your_product_table.ProductID
      AND UPPER(Stock_location) = 'MIAMI'
      AND UPPER(On_Sale) = 'Y'
  );

How this works: #

• First, we filter for all products on sale in Chicago.
• Then, we exclude any product that has a matching entry in Miami where it's also on sale (the NOT EXISTS subquery checks for this).
• This achieves the same result as the join method but focuses on the exclusion logic directly.

Sample Output from Your Data #

Based on the partial data you provided, the results would include:

• ProductID 2203 (Frozen Food): On sale in Chicago, not on sale in Miami
• ProductID 2206 (Snacks): Assuming Miami's entry isn't marked as on sale (since your sample cuts off), this would also be included

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