Using Like in JOIN

While reading “The Cartesian product: a feature, not a bug” by Peter Vogel, I remember that I used one more syntax to join 2 tables. From the article – we can join 2 tables using function in expression:

SELECT Table1.Field1, *
FROM Table1, Table2Num
WHERE Table1.Field1=CStr([Field2])

But you can also use same expression in FROM clause:

SELECT Table1.Field1, *
FROM Table1 INNER JOIN Table2 ON Table1.Field1=CStr(Table2.[Field2])

So you now can use Left or Right join:

SELECT Table1.Field1, *
FROM Table1 LEFT JOIN Table2 ON Table1.Field1=CStr(Table2.[Field2])

And you can use whatever expression there, most useful for me was LIKE, for example you define mask for objects in table1, like AB??01??, and your query returns all objects with code for that pattern:

SELECT Table1.Field1, Table2.*
FROM Table1 LEFT JOIN Table2 ON Table2.[Field2] LIKE Table1.Field1

Of course, do not switch query to design view, then your SQL will gone…