Db2 LUW SQL use of ANY

Peter Schwarcz

Db2 LUW SQL use of ANY
Can someone explain the use of the ANY in the middle of the following
delete statement?

DELETE
FROM DOCTABLE t
WHERE t.DOCREFID = ANY
(SELECT s.ID
FROM DOCTABLE s
WHERE s.DOCID = ?
AND s.SECTION = ?
AND s.VERSION = ?)
AND t.RENDITION <> ?
AND t.DOCCOLUMN IS NOT NULL

What does the ANY do ?

Thanks
Peter Schwarcz

David Williams

Db2 LUW SQL use of ANY
(in response to Peter Schwarcz)
Hi,

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_qualifiedpredicate.html

"
When the operator is SOME or ANY, the result of the predicate is:

True – if the specified relationship is true for at least one value returned by the fullselect.
False – if the result of the fullselect is empty or if the specified relationship is false for every value
returned by the fullselect.
Unknown – if the specified relationship is not true for any of the values returned by the fullselect and at least
one comparison is unknown because of a null value.
"

Regards,
David.

> On 12 May 2020 at 11:05 Peter Schwarcz <[login to unmask email]> wrote:
>
>
> Can someone explain the use of the ANY in the middle of the following
> delete statement?
>
> DELETE
> FROM DOCTABLE t
> WHERE t.DOCREFID = ANY
> (SELECT s.ID
> FROM DOCTABLE s
> WHERE s.DOCID = ?
> AND s.SECTION = ?
> AND s.VERSION = ?)
> AND t.RENDITION <> ?
> AND t.DOCCOLUMN IS NOT NULL
>
> What does the ANY do ?
>
> Thanks
> Peter Schwarcz
>
> -----End Original Message-----

Daniel Luksetich

Db2 LUW SQL use of ANY
(in response to Peter Schwarcz)
Same as EXISTS or IN. Also true in Db2 for z/OS.



ALL is a bit weirder



Cheers,

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Peter Schwarcz <[login to unmask email]>
Sent: Tuesday, May 12, 2020 5:05 AM
To: [login to unmask email]
Subject: [DB2-L] - Db2 LUW SQL use of ANY



Can someone explain the use of the ANY in the middle of the following delete statement?



DELETE
FROM DOCTABLE t
WHERE t.DOCREFID = ANY
(SELECT s.ID
FROM DOCTABLE s
WHERE s.DOCID = ?
AND s.SECTION = ?
AND s.VERSION = ?)
AND t.RENDITION <> ?
AND t.DOCCOLUMN IS NOT NULL



What does the ANY do ?



Thanks

Peter Schwarcz



-----End Original Message-----

Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

David Simpson

Db2 LUW SQL use of ANY [EXTERNAL]
(in response to Peter Schwarcz)
Haven’t seen this in a while… it means “equal to any of these values”

Not sure how/if that’s different from just using IN

From: Peter Schwarcz [mailto:[login to unmask email]
Sent: Tuesday, May 12, 2020 4:05 AM
To: [login to unmask email]
Subject: [DB2-L] - Db2 LUW SQL use of ANY [EXTERNAL]

Can someone explain the use of the ANY in the middle of the following delete statement?

DELETE
FROM DOCTABLE t
WHERE t.DOCREFID = ANY
(SELECT s.ID
FROM DOCTABLE s
WHERE s.DOCID = ?
AND s.SECTION = ?
AND s.VERSION = ?)
AND t.RENDITION <> ?
AND t.DOCCOLUMN IS NOT NULL

What does the ANY do ?

Thanks
Peter Schwarcz

-----End Original Message-----
________________________________
Please note: This message originated outside your organization. Please use caution when opening links or attachments.

Martin Hubel

Db2 LUW SQL use of ANY [EXTERNAL]
(in response to David Simpson)
David is correct. On its own, = can only accept 1 value. = ANY allows more
than one value.

============================================
Martin Hubel
MHC Inc.

[login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel

IBM Gold Consultant
IBM Champion 2008-2020

Host of the DB2Night Show
http://www.dbisoftware.com/db2nightshow/

My 19 Db2 Certifications include:
Db2 LUW 9.7 Advanced DBA
Db2 LUW 10.1 & 10.5 DBA
Db2 z/OS 10 & 11 DBA
Db2 9.7 Solutions Developer
============================================


On Tue, May 12, 2020 at 10:09 AM Simpson, David <[login to unmask email]>
wrote:

> Haven’t seen this in a while… it means “equal to any of these values”
>
>
>
> Not sure how/if that’s different from just using IN
>
>
>
> *From:* Peter Schwarcz [mailto:[login to unmask email]
> *Sent:* Tuesday, May 12, 2020 4:05 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Db2 LUW SQL use of ANY [EXTERNAL]
>
>
>
> Can someone explain the use of the ANY in the middle of the following
> delete statement?
>
>
>
> DELETE
> FROM DOCTABLE t
> WHERE t.DOCREFID = ANY
> (SELECT s.ID
> FROM DOCTABLE s
> WHERE s.DOCID = ?
> AND s.SECTION = ?
> AND s.VERSION = ?)
> AND t.RENDITION <> ?
> AND t.DOCCOLUMN IS NOT NULL
>
>
>
> What does the ANY do ?
>
>
>
> Thanks
>
> Peter Schwarcz
>
>
> -----End Original Message-----
> ------------------------------
>
> Please note: This message originated outside your organization. Please use
> caution when opening links or attachments.
>
> -----End Original Message-----
>

Michael Hannan

RE: Db2 LUW SQL use of ANY [EXTERNAL]
(in response to Martin Hubel)

There is little to add perhaps. Since Basic SQL syntax works on any environment, I extracted this little table from the SQL Ref Guide (Db2 zOS), to help understand these strange "Quantified" Predicates that are "never" used, well not very often, unless someone wants to confuse the reader:

 

Table 61. IN predicate and equivalent quantified predicates
IN predicate Equivalent quantified predicate

expression IN (fullselect1) expression = ANY (fullselect1)
expression NOT IN (fullselect1) expression <> ALL (fullselect1)

I am not a big fan of  IN  subqueries where they are correlated unless DB2 can transform the SQL into another construct. There I would also not be a big fan of the Quantified versions with correlation (even less so). Any IN subquery that cannot be transformed to a join or EXISTS variation can be dangerous if the resulting IN list is large. Any NOT IN subquery with a large list can be dangerous.

While I never code '= ANY', it is quite rare I code IN with subquery unless am feeling lazy (and I am confident of a good access path). Much more likely I would code a join, or EXISTS with a good probe index available.

What I am finding to be quite powerful these days, is join to a correlated subquery, and I use in my own longer running queries (not for trivial queries) often:

JOIN TABLE   
(SELECT    cols
    from TBL
   Where predicates
   AND   correlation predicate
   ORDER BY  cols
   FETCH FIRST 1 ROW ONLY
)  AS X
ON Join Predicates

Early days this created woeful performing access paths, but is pretty good in the modern day, thanks to Optimizer team.

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ May 15, 2020 - 10:09 PM (Australia/Melbourne)