When to use a CROSS JOIN, and grouping rows of text together using the STRING_AGG function.

You should avoid the CROSS JOIN unless you have a good reason. But what are those reasons?
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/que...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/dat...
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql...
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql...
SQL Server Integration Services (SSIS): rebrand.ly/sql...
SQL Server Analysis Services (SSAS): rebrand.ly/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
----
Unlike the other joins, CROSS JOINs does not use the ON clause to compare rows. Instead, CROSS JOINS takes each row in one table, and joins it to every row in the second table. This means that the number of rows in the resulting query can be massive.
In this video, we will look at animals in a zoo, and ask the question: what animals has each animal seen, based on when they were at the zoo? They would not have seen animals which arrived after they left, or left before they arrived.
We'll look at answering this question using a CROSS JOIN. We'll also tidy it up using the STRING_AGG function, which was introduced in SQL Server 2017.
Here is the code to generate the ZooAnimals table:
DROP TABLE IF EXISTS ZooAnimals;
CREATE TABLE ZooAnimals
(ID int primary key,
Animal varchar(10),
StartDate date,
EndDate date);
INSERT INTO ZooAnimals
VALUES (1, 'Lion', '2023-01-01', '2023-06-30'),
(2, 'Tiger', '2023-03-01', '2023-05-30'),
(3, 'Elephant', '2023-06-01', '2024-01-31'),
(4, 'Monkey', '2023-05-01', '2023-05-31'),
(5, 'Giraffe', '2023-08-01', '2023-12-31');

[@supersql8406]

Jeeze, finally someone did a Cross Join tutorial with an elegant explanation!

[@SQLServer101]

Hi SuperSQL. Glad you liked it! Phillip

[@WillijahDawson]

Great explanation i appreciate the excel comparison

[@vickarkhan]

Such a beautiful explanation. Thanks for the video.

[@user-th9rk5iy1d]

As I heard the voice instantly I knew it is Mr. Burton, Thanks for your udemy course.

[@abdulkadirdemirci3242]

wonderfull explanation on that topic with perfect example as wondefull as your expression, best regards

[@basheeral-momani2032]

thank you for the butifull example and explanation

[@minnrick7986]

Superb explanation, thank you.

[@vijayanandp8804]

Awesome example that thank you

[@BTS-en1db]

Thanks for the vedio

[@sinjansengupta178]

can't it be done using self join concept?

[@Retrosenescent]

Why did you use a cross join and then filter rather than just using an inner join?

[@SQLServer101]

Hi Emberfly. That is another option. You would put all of the WHERE into the ON. If you wanted all of the rows in Table1 matched against all of Table2 (as per 3' of this video), then you can't do that with an INNER JOIN easily. Phillip

[@petrdanilov3122]

Great