I had never heard of a cross join, but was exploring ways to put some complex calculations into other calculations in a nested fashion on SQL server. SQL does not allow you to reference a calculated column to calculate another column, but this method allows you to add in calculated columns you can then use in the top level query to calculate by the name of the calculated column instead of repeating the calculation over and over again.

Here is an article I generated based on this idea with CoPilot about Cross Joins:

When working with SQL Server, understanding the different types of joins is essential for writing efficient and expressive queries. One of the lesser-used but powerful join types is the CROSS JOIN. In this article, we’ll explore what a CROSS JOIN is, how it works, and two practical use cases: generating Cartesian products and referencing calculated columns without repeating logic.

---

🔄 What is a CROSS JOIN?

A CROSS JOIN returns the Cartesian product of two tables. This means that each row from the first table is combined with every row from the second table. If Table A has 3 rows and Table B has 4 rows, the result will be 3 × 4 = 12 rows.

Basic Syntax:

SELECT *
FROM TableA
CROSS JOIN TableB;

Unlike INNER or OUTER JOINs, a CROSS JOIN does not require an ON clause because it doesn’t match rows based on any condition.

---

📊 Example 1: Generating a Cartesian Product

Let’s say you want to generate all possible combinations of colors and sizes for a product catalog.

Sample Tables:

Colors

ColorID	ColorName
1	Red
2	Blue

Sizes

SizeID	SizeName
A	Small
B	Large

Query:

SELECT ColorName, SizeName
FROM Colors
CROSS JOIN Sizes;

Result:

ColorName	SizeName
Red	Small
Red	Large
Blue	Small
Blue	Large

This is a classic use of CROSS JOIN to generate all possible combinations between two sets.

---

🧮 Example 2: Reusing Calculated Columns with CROSS JOIN

Another powerful use of CROSS JOIN is to reference calculated values multiple times in a query without repeating the logic.

Scenario

You have a Sales table and want to calculate tax and total amount for each sale. Instead of repeating the tax formula in multiple places, you can calculate it once in a derived table and CROSS JOIN it back.

Sample Table:

CREATE TABLE Sales (
    SaleID INT,
    Amount DECIMAL(10, 2)
);

Query:

SELECT 
    s.SaleID,
    s.Amount,
    calc.Tax,
    calc.Total
FROM Sales s
CROSS JOIN (
    SELECT 
        s1.SaleID,
        s1.Amount * 0.10 AS Tax,
        s1.Amount + (s1.Amount * 0.10) AS Total
    FROM Sales s1
) calc
WHERE s.SaleID = calc.SaleID;

Why This Works

• The subquery calc computes the Tax and Total once.
• The CROSS JOIN allows you to reference those values without duplicating the formulas.
• The WHERE clause ensures that only matching rows are returned, effectively simulating an INNER JOIN.

---

⚠️ A Word of Caution

CROSS JOINs can produce very large result sets, especially with large tables. Always test your queries with limited data first to avoid performance issues or overwhelming your system.

---

✅ Final Thoughts

While CROSS JOINs are not as commonly used as INNER or OUTER JOINs, they offer unique advantages in specific scenarios. Whether you’re generating combinations or simplifying complex calculations, understanding how and when to use CROSS JOIN can make your SQL queries more efficient and maintainable.

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

Subscribe

We won't send you spam. Unsubscribe at any time.

Built with Kit