UNION vs JOIN vs NOT EXISTS

By /

SQL Study Notes – AdventureWorksDW2017

1. UNION and UNION ALL

UNION combines results from two or more queries and removes duplicates.
UNION ALL combines results and keeps duplicates (faster).

Example – Combine Product Subcategories and Categories:

SELECT 
    'Subcategory' AS Type,
    PSC.ProductSubcategoryKey AS ID,
    PSC.EnglishProductSubcategoryName AS Name
FROM DimProductSubcategory PSC

UNION ALL

SELECT 
    'Category' AS Type,
    PC.ProductCategoryKey AS ID,
    PC.EnglishProductCategoryName AS Name
FROM DimProductCategory PC
ORDER BY Type ASC, ID ASC, Name ASC;

2. When to Use UNION

  • When you want to combine similar data from different tables or the same table with different conditions
  • When you need to stack results vertically

3. UNION vs JOIN vs NOT EXISTS

Method Purpose Use When
UNION / UNION ALL Combine rows vertically Same structure, different conditions
INNER JOIN Combine columns horizontally (matching records) You need data from both tables together
LEFT JOIN + IS NULL Find records that do NOT exist in the other table “Never sold” or “no claims” questions
NOT EXISTS Find records that do NOT exist (subquery version) Alternative to LEFT JOIN IS NULL

4. Practical Example – “Bikes Never Sold”

Using LEFT JOIN + IS NULL:

SELECT P.ProductKey, P.EnglishProductName
FROM DimProduct P
INNER JOIN DimProductSubcategory PSC ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
INNER JOIN DimProductCategory PC ON PSC.ProductCategoryKey = PC.ProductCategoryKey
LEFT JOIN FactInternetSales FIS ON P.ProductKey = FIS.ProductKey
WHERE PC.EnglishProductCategoryName = 'Bikes'
  AND FIS.ProductKey IS NULL;

Using NOT EXISTS:

SELECT P.ProductKey, P.EnglishProductName
FROM DimProduct P
INNER JOIN DimProductSubcategory PSC ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
INNER JOIN DimProductCategory PC ON PSC.ProductCategoryKey = PC.ProductCategoryKey
WHERE PC.EnglishProductCategoryName = 'Bikes'
  AND NOT EXISTS (
    SELECT 1 FROM FactInternetSales FIS WHERE FIS.ProductKey = P.ProductKey
);
Scroll to Top