I have two tables that I want to join. It works without where conditions. After adding where conditions, I got syntax error near a (where I give table1 an alternative name). From my understanding, the syntax looks correct?

My query

select * from table1 where date >= '2020-10-01' and date <= '2020-10-31' a
  left join table2 b where registered >= '2020-10-01' and registered <= '2020-10-31' b
      on a.id = cast(b.id as varchar)

CodePudding user response：

Some issues:

• where goes after all tables (and their join conditions)
• aliases go immediately after the table name

Applying these two corrections and some formatting:

select *
from table1 a
left join table2 b on a.id = cast(b.id as varchar)
  and registered >= '2020-10-01' and registered <= '2020-10-31'
where date >= '2020-10-01' and date <= '2020-10-31'

Conventionally, join conditions that describe access to joined rows (typically the keys) are coded first, then filtering conditions (ones involving only columns in the joined table) are coded last.

Which can be slightly simplified using between to:

select *
from table1 a
left join table2 b on a.id = cast(b.id as varchar)
  and registered between '2020-10-01' and '2020-10-31'
where date between '2020-10-01' and '2020-10-31'