Page 127 - SQL

d.Name e.FName

Tech James

Tech John

Tech Michael

Tech Johnathon

It is recommended to write an explicit CROSS JOIN if you want to do a cartesian join, to highlight
that this is what you want.

Joining on a Subquery

Joining a subquery is often used when you want to get aggregate data from a child/details table
and display that along with records from the parent/header table. For example, you might want to
get a count of child records, an average of some numeric column in child records, or the top or
bottom row based on a date or numeric field. This example uses aliases, which arguable makes
queries easier to read when you have multiple tables involved. Here's what a fairly typical
subquery join looks like. In this case we are retrieving all rows from the parent table Purchase
Orders and retrieving only the first row for each parent record of the child table
PurchaseOrderLineItems.

SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo,
item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN
(
SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Min(l.id) as Id
FROM PurchaseOrderLineItems l
GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
) AS item ON item.PurchaseOrderId = po.Id

CROSS APPLY & LATERAL JOIN

A very interesting type of JOIN is the LATERAL JOIN (new in PostgreSQL 9.3+),
which is also known as CROSS APPLY/OUTER APPLY in SQL-Server & Oracle.

The basic idea is that a table-valued function (or inline subquery) gets applied for every row you
join.

This makes it possible to, for example, only join the first matching entry in another table.
The difference between a normal and a lateral join lies in the fact that you can use a column that
you previously joined in the subquery that you "CROSS APPLY".

Syntax:

PostgreSQL 9.3+

https://riptutorial.com/ 109