A CROSS JOIN, also referred to as CARTESIAN JOIN returns all possible combinations of rows from both tables
The CARTESIAN JOIN or CROSS JOIN returns the cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.
A CROSS JOIN returns all possible combinations of rows from both tables.
A cross join matches all possible combinations of rows from the tables being joined. In practice, it's rarely used; but there are some specialized cases where it is useful.
1. Replace the existing query with the following code:
```
SELECT p.Name, c.FirstName, c.LastName, c.EmailAddress
FROM SalesLT.Product AS p
CROSS JOIN SalesLT.Customer AS c;
```
2. Run the query and note that the results contain a row for every product and customer combination (which might be used to create a mailing campaign in which an indivdual advertisement for each product is emailed to each customer - a strategy that may not endear the company to its customers!).
-- CROSS JOIN
-- Every product/city combination
SELECT p.Name AS Product, a.City
FROM SalesLT.Product AS p
CROSS JOIN SalesLT.Address AS a;
////////////////////
C. Use the SQL-92 CROSS JOIN syntax
The following example returns the cross product of the two tables Employee and Department in the AdventureWorks2022 database. A list of all possible combinations of BusinessEntityID rows and all Department name rows are returned.
SELECT e.BusinessEntityID,
d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.BusinessEntityID,
d.Name;
.
//////////////
///////////////////New Copy Code //////////////////////////
///////////////////////////////////////
///////////////////// PUB Database Examples ////////////////////////////
How to Use Cross Joins in SQL
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The example below returns the cross product of the two tables authors and publishers. This query will return a list of all possible combinations of au_lname rows and all pub_name rows.
The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. This is an example of a Transact-SQL cross join:
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC
The result set contains 184 rows (authors has 23 rows and publishers has 8; 23 multiplied by 8 equals 184).
However, if a WHERE clause is added, the cross join behaves as an inner join. For example, these Transact-SQL queries produce the same result set:
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
WHERE authors.city = publishers.city
ORDER BY au_lname DESC
-- Or
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors INNER JOIN publishers
ON authors.city = publishers.city
ORDER BY au_lname DESC
Here is the result set:
///////////////////////////
Related Content
