Run3Pal avatar image
Run3Pal asked

Deciphering SQL Code

Hello, I am currently reviewing a query without access to the databases on which the query is performed. (It's not ideal but that's what I am tasked with). I am not a SQL expert and trying to identify what the below code does as I cannot run the query. It is reading from and writing to the same temp table (duplicating?). I don't know what the source of 'Y' is or what the end result is. Any help is appreciated. Thank you. INSERT INTO #temp1 SELECT X.CURSTATUS ,X.GENDER ,Y.PACKAGE ,X.AGE ,1 AS factor1 ,1 AS factor2 FROM #temp1 X WITH (NOLOCK) ,( SELECT 'P1' AS PACKAGE UNION ALL SELECT 'P2' ) Y WHERE X.PACKAGE = 'P5';
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Oleg avatar image
Oleg answered
The alias named Y is the "table" consisting of one column named Package and 2 rows. The value of the Package column in first row is P1 and the second row is P2. This set is created by the means of running the part reading SELECT 'P1' AS PACKAGE UNION ALL SELECT 'P2' If you were to execute the statement displayed above, you would get the following result: PACKAGE ------- P1 P2 Please note that this select does not query any existing tables, it is merely creating the one column/2 rows set of data, that is all. There is a comma in the FROM clause between the temp table named #temp1 which is aliased as X and the select displayed above which is aliased as Y. This comma is used to identify the JOIN between X and Y. This join does not have any join condition, so it is a cross join. The way it is written is very old school, and should be avoided if at all possible. In nowadays, most select statements which involve the join between 2 tables are written properly by using the word (INNER or LEFT or RIGHT) JOIN between the tables (rather than comma) and then specifying the join condition in the ON clause. The join without any join conditions is called CROSS JOIN. It produces the match for every combination of the rows in 2 tables, and is also called Cartesian join/product. For example, suppose there are 100 records in first table and 100 records in the second table. When cross joined, they will return 100 x 100 = 10,000 records in the result. In the cases of your query, what happens is the following: - There are some records in the temp table where the value in Package column is P5. - These records are cross joined with the created on the fly 1 column/2 rows "table" which produces twice as many rows as the number of rows in the temp table where Package = P5 - Now these records (2 records for each record in the temp table) are inserted back into the temp table, but with value of P1 and P2 in each inserted records. In other words, this statement picks the rows from the temp table where Package = P5 and for each such record it inserts the record with Package = P1 and record wich Package = P2 back into it. So, if before this statement this table had 100 records where Package = P5, it now has 100 new records with Package = P1 and 100 new records with Package = P2. When inserting, it uses the original (source) values in CURSTATUS, GENDER and AGE columns. Here is the same script restated to use the CROSS JOIN in place of the comma-delimited table list (which is a very old syntax that should have been deprecated long time ago, but is still used by some). insert into #temp1 select X.CURSTATUS ,X.GENDER ,Y.PACKAGE ,X.AGE ,1 as factor1 ,1 as factor2 from #temp1 as X with (nolock) -- not sure why the nolock hint is here, it is not needed cross join /* cross join is used to create 2 rows for each row in temp table */ (select 'P1' as PACKAGE union all select 'P2') as Y where X.PACKAGE = 'P5'; Hope this explanation is not too long :) Oleg
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Oleg. Your reply is very detailed and helpful
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.