Thanks Mike.
I have been using SQL Set operations for a long time, but your book does
provide nice examples of LATERAL and CROSS JOIN that I have not been
familiar with.
Jeff Young
Sr. Programmer Analyst
You're welcome!
It took me a while to notice LATERAL joins too and it was a bright light
bulb moment when I finally noticed and wrapped my head around them. I use
them a lot now. I wish I had started using them earlier since some code
I'd built previously could have been simplified using them. I refactor
said older code when I have a reason to work on it otherwise.
CROSS JOIN LATERAL to a subquery using correlation names and row matching
is different than the standard CROSS JOIN.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Standard CROSS JOIN produces a cartesian product (all combinations of rows)
of two row sets:
1) The set of rows prior to the CROSS JOIN, combined with
2) The set of rows in the table being CROSS JOINed to.
CROSS JOIN LATERAL to a subquery using correlation names, coupled with row
matching in the subquery, produces this combination of rows:
a) Each row in the set PRIOR to the CROSS JOIN LATERAL, is combined with
b) Each row in the CROSS JOIN LATERAL subquery that matches based on the
subquery WHERE clause (that uses correlation name column references to
columns from earlier in the set).
In short, the typical use of a CROSS JOIN LATERAL is like a CROSS JOIN,
except row matching / filtering is applied to produce a set of rows less
than a standard full cartesian product. Because row matching is used, it
is very much like an INNER JOIN, except an INNER JOIN does row matching in
the ON clause, whereas CROSS JOIN LATERAL does its row matching in the
subquery WHERE clause, thereby not needing an ON clause (which would be
redundant row matching logic, and would violate CROSS JOIN syntax).
I find a lot of uses for CROSS JOIN LATERAL, whereas rarely need the true
cartesian product of a standard CROSS JOIN. I do use standard CROSS JOIN a
lot when the table being joined to, by design, always contains one row.
I've been trying to grasp LATERAL joins for a few months now. It looks
like this should provide a good basis.
A LATERAL join is just a join to a subquery with one enhancement over a
standard join to a subquery. The subquery of a LATERAL join can, using
correlation names, reference columns produced earlier in the result set,
whereas WITHOUT the LATERAL keyword on the join, the subquery being joined
to CANNOT reference columns produced earlier. When the subquery being
LATERAL joined to contains ORDER BY and FETCH FIRST x ROWS ONLY clauses, it
gives you complete control over which row(s) to join to when multiple rows
are present to choose from. There are plenty of ways to code that same
thing in SQL, but the other methods tend to be a larger volume of code, and
tend to run slower.
If you're hesitant to use LATERAL JOINs, don't be. IBM uses them in some
of their System Catalog objects. Granted, some of those System Catalog
objects are slow, and some are fast. The slow ones have nothing to do with
the use of LATERAL joins. The slow ones are built over some legacy objects
that either need to be refactored or bypassed/replaced.
I noticed that this was "Part 1". Is this a tease for the rest of
the book? ;-)
I'll definitely produce a lot more advanced content regarding SET based
SQL. A lot more content is needed to cover the subject thoroughly. More
elaboration is needed on building complex result sets, and how to take
those SET based / built result sets and perform SET based INSERTs, UPDATEs,
DELETEs, and MERGEs. I'll also delve a lot into using SQL scalar and table
functions, since I believe there is a lot of power there that is
underutilized. You can easily experience performance problems fetching
data using SQL functions, so you have to be very careful and selective in
doing that. However, SQL and external scalar and table functions work
well, for some applications, subject to a few limitations, for calling CL
and RPG commands and programs, calling certain OS API's, performing
calculations, running SQL scripts/statements, creating, modifying, or
dropping objects, and general updating of the database. SQL stored
procedures seem to get a lot more attention than SQL functions, yet
functions can be invoked in much more flexible ways than procedures.
Unless you wrap a call to an SQL stored procedure inside an SQL function,
you have to "CALL" a procedure, whereas functions can be invoked in many
places of a query, in the middle of result set generation. SQL stored
procedures have their advantages too, so I'm not saying don't use them. In
particular, with one CALL, they can return multiple result sets. They also
provide a nice way of encapsulating the database. There are a lot of SQL
training resources out there, but my intent is to only focus on advanced
subject areas that, in my opinion, would benefit from more coverage. That
content may come in the form of one or more books, a website, or both.
Mike
midrange.com
