SQL - Testing Left Join with Conditions

LeftJoinsWithConditions.sql

drop table if exists #customers, #orders
create table #customers (CustomerId int, CustomerName nvarchar(50))
create table #orders (OrderId int, CustomerId int, OrderDate date)

insert into #customers (CustomerId, CustomerName) 
	values (1, 'CustomerOne'),
	       (2, 'CustomerTwo'),
		   (3, 'CustomerThree')
insert into #orders (OrderId, CustomerId, OrderDate)
	values (1, 1, '2000-01-01'),
		   (2, 2, '2000-01-05'),
		   (3, 1, '2000-01-10'),
		   (4, 1, '2000-01-15'),
		   (5, 1, '2000-01-18')

-- -------------------------------
-- -------------------------------
-- -------------------------------

select c.*, o.OrderId, o.OrderDate 
from #customers c
left join #orders o
on c.CustomerId = o.CustomerId
and o.OrderDate > '2000-01-10'

select c.*, o2.OrderId, o2.OrderDate 
from #customers c
left join (select * from #orders o 
		where o.OrderDate > '2000-01-10') o2
on c.CustomerId = o2.CustomerId

--  both the above queries return:
--	---------- ------------  ------- ----------
--	CustomerId CustomerName  OrderId OrderDate
--	---------- ------------  ------- ----------
--	1		   CustomerOne   4       2000-01-15
--	1		   CustomerOne   5       2000-01-18
--	2          CustomerTwo   null    null
--	3          CustomerThree null    null

-- This is ALL customers, matched with
-- any orders after 2000-01-10

-- -------------------------------
-- -------------------------------
-- -------------------------------

select c.*, o.OrderId, o.OrderDate 
from #customers c
left join #orders o
on c.CustomerId = o.CustomerId
where o.OrderDate > '2000-01-10'

--  this query returns:
--	---------- ------------  ------- ----------
--	CustomerId CustomerName  OrderId OrderDate
--	---------- ------------  ------- ----------
--	1		   CustomerOne   4       2000-01-15
--	1		   CustomerOne   5       2000-01-18

-- This is ONLY customers with orders 
-- after 2000-01-10 (effectively, an inner join)