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.