Lateral joins aren’t a feature used in SQL Server, but I’ve run across them with both Oracle and Postgres. A Lateral join provides similar functionality as a APPLY in SQL Server, where the items on the APPLY/LATERAL side are referring to values from the FROM table.
Here’s an example of use of LATERAL. We’ll take a table of sales records.
CREATE TABLE public.sales( sales_id int GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, customer_name varchar(20) NOT NULL, sales_date date NOT NULL, sales_amount numeric NOT NULL ); INSERT INTO public.sales(customer_name, sales_date, sales_amount) VALUES ('Acme', '2021-09-01', 100.00), ('Acme', '2021-09-02', 120.00), ('Acme', '2021-09-03', 150.00), ('Smith Bait Shop', '2021-08-29', 50.25), ('Smith Bait Shop', '2021-08-31', 75.50), ('Smith Bait Shop', '2021-09-02', 90.75); SELECT * FROM public.sales;
We want a query to show each sale, as well as the date and amount of the last sale made to that customer.
SELECT s.customer_name, s.sales_date, s.sales_amount, l.sales_date as last_sales_date, l.sales_amount as last_sales_amount FROM public.sales as s LEFT JOIN LATERAL ( SELECT t.sales_date, t.sales_amount FROM public.sales as t WHERE t.customer_name = s.customer_name AND t.sales_date < s.sales_date ORDER BY t.sales_date DESC LIMIT 1 ) as l ON true ORDER BY s.customer_name, s.sales_date;
Using the LATERAL JOIN, we can create a dataset to find the previous sale recond for each sale.
If we only needed one value, either the previous date or amount, we could write a subquery to get that one value, but the LATERAL will allow us to get multiple values from the derived dataset.