Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?

just out of curiosity can you ask him which one he believes will run faster and why :)

În mie., 1 feb. 2023 la 21:27, kyle Hailey <kylelf_at_gmail.com> a scris:

>
> chat GPT says:
> what is the difference between the following two SQL queries?
>
> It is important to note that the first query will only return one row, as
> it is retrieving the row with the maximum ID value, while the second
> query will return multiple rows if there are multiple rows in the F_PLAN
> table with the same COL3 value in the list ('some_guid_id_1',
> 'some_guid_id_2').
>
> Both queries will return the same data, as long as the data in the F_PLAN
> table does not change between the two queries. The difference lies in the
> way the data is filtered and retrieved from the F_PLAN table.
>
> Query 1 uses a subquery to get the maximum ID of the F_PLAN table where
> the COL3 value is in the list of values ('some_guid_id_1',
> 'some_guid_id_2'). The main query then retrieves all columns of the F_PLAN
> table where the ID is equal to the maximum ID from the subquery.
>
> Query 2 uses a derived table t1 to get the COL3 and ID of the F_PLAN
> table where the COL3 value is in the list of values ('some_guid_id_1',
> 'some_guid_id_2'). The main query then joins this derived table with the
> F_PLAN table on the COL3 and ID columns.
>
> In both cases, the result set will contain the columns COL1, COL2, COL3,
> ID, COL4, and VERSION from the F_PLAN table, filtered by the COL3 values
> in the list ('some_guid_id_1', 'some_guid_id_2'). The resulting data will
> be ordered by ID.
>
>
>
> On Wed, Feb 1, 2023 at 1:00 PM Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> That's what my gut was saying also - but having a devil of a time turning
>> it into an equivalent version to get rid of the correlated subquery.
>>
>> Thanks,
>> Chris
>>
>>
>> On Wed, Feb 1, 2023 at 11:11 AM Dominic Brooks <dombrooks_at_hotmail.com>
>> wrote:
>>
>>> No. Not equivalent. But they might appear to be depending on what data
>>> you run it with.
>>>
>>> There might be constraints etc which mean they return the same results
>>> under your circumstances. But semantically they are not equivalent.
>>>
>>>
>>>
>>>
>>>
>>> *From: *Chris Taylor <christopherdtaylor1994_at_gmail.com>
>>> *Sent: *01 February 2023 15:40
>>> *To: *oracle-l_at_freelists.org
>>> *Subject: *Sanity Check - Correlated Select Subquery SQL rewrite
>>> equivalent? Or no?
>>>
>>>
>>>
>>> Hey guys, just need a quick sanity check here.
>>>
>>> Are these queries equivalent? My gut says not exactly but maybe. The
>>> results returned are identical in the test cases I ran, but I fear I'm
>>> missing something.
>>>
>>> Original SQL (27 seconds to return 26 rows)
>>> Modified Version - Same rows but 336 ms
>>>
>>>
>>>
>>> Original
>>>
>>> Modified
>>>
>>> SELECT FPE.COL1,
>>>
>>> FPE.COL2,
>>>
>>> FPE.COL3,
>>>
>>> FPE.ID
>>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>>> ,
>>>
>>> FPE.COL4,
>>>
>>> FPE.VERSION
>>>
>>> FROM F_PLAN FPE
>>>
>>> WHERE (FPE.ID
>>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>>> IN
>>>
>>> (SELECT MAX (FPE.ID
>>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>>> )
>>>
>>> FROM F_PLAN
>>>
>>> WHERE FPE.COL3 IN
>>>
>>> ('some_guid_id_1',
>>>
>>> 'some_guid_id_2')))
>>>
>>> ORDER BY FPE.ID
>>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>>>
>>> SELECT FPE.COL1,
>>>
>>> FPE.COL2,
>>>
>>> FPE.COL3,
>>>
>>> FPE.ID
>>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>>> ,
>>>
>>> FPE.COL4,
>>>
>>> FPE.VERSION
>>>
>>> FROM F_PLAN FPE,
>>>
>>> (SELECT COL3, ID
>>>
>>> FROM F_PLAN
>>>
>>> WHERE COL3 IN
>>>
>>> ('some_guid_id_1',
>>>
>>> 'some_guid_id_2')
>>>
>>> ) t1
>>>
>>> where FPE.COL3 = T1.COL3
>>>
>>> and FPE.ID
>>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>>> = T1.ID
>>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft1.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=HQamwon1ueUsJNjPKU2%2FZiWw0CQtDQZQk7xOBvvGKjs%3D&reserved=0>
>>>
>>> ORDER BY FPE.ID
>>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>>>
>>>
>>>
>>>
>>>
>>

--
http://www.freelists.org/webpage/oracle-l