When to use a CROSS JOIN, and grouping rows of text together using the STRING_AGG function.
7.5 هزار بار بازدید -
3 سال پیش
-
You should avoid the CROSS
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/querying-microsoft-sql-server
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpivot-ssas-tabular-dax
----
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');
3 سال پیش
در تاریخ 1400/11/07 منتشر شده
است.
7,566
بـار بازدید شده