Oracle: Syntax for multiple lateral joins or applys

Question

I am trying to get the syntax for a series of lateral joins in Oracle.

I have an authors table with the author’s id, name and home; I have a books table with an id, authorid which is a foreign key to books(id), a title and price.

I want to use lateral joins to calculate the tax (price*0.1) and the inclusive price (price+tax), as well as to include the author’s name and home.

Here is a version that works in PostgreSQL and MySQL:

SELECT
    id,title,
    price, tax, inc,
    givenname, othernames, familyname,
    home
FROM books
    JOIN LATERAL(SELECT price*0.1) AS sq(tax) ON true
    JOIN LATERAL(SELECT price+tax) AS sq2(inc) ON true
    LEFT JOIN LATERAL(SELECT givenname,othernames,familyname FROM authors
        WHERE authors.id=books.authorid) AS sw2 ON true;

Here’s the same thing in MSSQL:

SELECT
    id,title,
    price, tax, inc,
    givenname,othernames,familyname,
    home
FROM books
    CROSS APPLY(SELECT price*0.1) AS sq(tax)
    CROSS APPLY(SELECT price+tax) AS sq2(inc)
    OUTER APPLY(SELECT givenname,othernames,familyname,home FROM authors
        WHERE authors.id=books.authorid) AS sw2;

I understand that Oracle has the CROSS APPLY and OUTER APPLY syntax, as well as the more standard LATERAL syntax. I also know about the following oracle quirks:

• SELECT … FROM dual
• you can’t use AS for table aliases

I have tried every combination of syntax I can think of but I keep getting cryptic errors such as SQL command not properly ended or MISSIING KEYWORD or something equally illuminating.

What is the correct syntax for this sort of query.

Note

I know that there are other ways to get the same result, and that it’s a trivial example. This is a question about how to write lateral joins in Oracle.

Update

OK, here’s the answer:

Oracle doesn’t like the syntax:

(SELECT price*0.1) sq(tax)

It prefers the alias to be in the subquery:

(SELECT price*0.1 AS tax)

Answer 1

If you really want to use a LATERAL join then:

SELECT b.id,
       b.title,
       b.price,
       t.tax,
       i.inc,
       a.givenname,
       a.othernames,
       a.familyname,
       a.home
FROM   books b
       CROSS JOIN LATERAL(SELECT price*0.1 AS tax FROM DUAL) t
       CROSS JOIN LATERAL(SELECT price+tax AS inc FROM DUAL) i
       LEFT OUTER JOIN authors a
       ON a.id=b.authorid;

Note: authors does not need to be a LATERAL join, and OUTER join is sufficient.

However, you don't need to use a LATERAL join:

SELECT b.id,
       b.title,
       b.price,
       b.price * 0.1 AS tax,
       b.price * 1.1 AS inc,
       a.givenname,
       a.othernames,
       a.familyname,
       a.home
FROM   books b
       LEFT OUTER JOIN authors a
       ON a.id=b.authorid;

Which, for the sample data:

CREATE TABLE books ( id, title, price, authorid ) AS
  SELECT 1, 'How to cook 4 humans', 42, 1 FROM DUAL UNION ALL
  SELECT 2, 'Mannequin Maintenance: For Dummies', 3.99, 2 FROM DUAL;

CREATE TABLE authors ( id, givenname, othernames, familyname, home ) AS
SELECT 1, 'A', 'Large', 'Monster', 'Cave' FROM DUAL;

Both queries output:

ID	TITLE	PRICE	TAX	INC	GIVENNAME	OTHERNAMES	FAMILYNAME	HOME
1	How to cook 4 humans	42	4.2	46.2	A	Large	Monster	Cave
2	Mannequin Maintenance: For Dummies	3.99	.399	4.389	null	null	null	null

fiddle