What is the meaning of this operator: *=

I have a very old Access application that uses SQL Pass-Through Queries. This application has been nursed along through multiple server upgrades, and we recently moved up to SQL 2012.

I now get a syntax error. The last two lines of the code are where the problem is. I believe it is some kind of JOIN, but I've never seen this syntax. Removing the "*" allows the query to run, but leaves out data. Is there a easy replacement of this syntax or do I need to redesign these queries using the JOIN phrasing?

`SELECT survey.hole,survey_date,pit,cu,rec,zn, mn,north,east,elevation,
rtrim(min_type) min_type,rtrim(rock_type) rock_type,
rtrim(alt_type) alt_type, rtrim(min_style) min_style
FROM survey,assay,geology
WHERE mined_out IS NULL
AND pit = (SELECT fmpit FROM tyrctl WHERE source = 'export by date')
AND (survey_date >= (SELECT fmdate FROM tyrctl WHERE source = 'export by date')
AND survey_date

Your query is using the pre-ANSI (and possibly original) JOIN syntax. Back in "the day", joining tables was done by specifying all of the tables, separated by commas, in the FROM clause, and then specifying the join condition(s) in the WHERE clause. Equating the current ANSI style with the old style gives you the following chart of options:

• [INNER] JOIN: TableA.Field = TableB.Field

• LEFT [OUTER] JOIN: TableA.Field *= TableB.Field

• RIGHT [OUTER] JOIN: TableA.Field =* TableB.Field

• FULL [OUTER] JOIN: TableA.Field = TableB.Field

• CROSS JOIN: {nothing specified to relate the two tables}

The pre-ANSI syntax is / was supported (sometimes with slight variation) by the various RDBMSes. However, it has been deprecated for a while in Microsoft SQL Server, and was officially removed in SQL Server 2012 (as you are now seeing). This was noted in the Discontinued Database Engine Functionality in SQL Server 2012 MSDN page (2nd to last row in the grid).

In your query, you have 3 tables:

FROM survey,assay,geology


and two LEFT JOIN conditions:

AND survey.hole *= assay.hole
AND survey.hole *= geology.hole


But yes, you can rewrite it as follows:

SELECT sy.hole, survey_date, pit, cu, rec, zn, mn, north, east, elevation,
rtrim(min_type) AS [min_type], rtrim(rock_type) AS [rock_type],
rtrim(alt_type) AS [alt_type], rtrim(min_style) AS [min_style]
FROM survey sy
LEFT JOIN assay ay
ON ay.hole = sy.hole
LEFT JOIN geology gy
ON gy.hole = sy.hole
WHERE mined_out IS NULL
AND pit = (SELECT fmpit FROM tyrctl WHERE source = 'export by date')
AND (
survey_date >= (SELECT fmdate FROM tyrctl WHERE source = 'export by date')
AND survey_date

Technically, you don't need the parenthesis around the two survey_date conditions in the WHERE clause as they are all AND conditions, but if you prefer it for readability then it is fine.

As @ypercube pointed out in a comment on this answer, the query can be further simplified to since the 3 subqueries in the WHERE clause all reference the same row of the same table. So that could look at follows:

SELECT sy.hole, survey_date, pit, cu, rec, zn, mn, north, east, elevation,
rtrim(min_type) AS [min_type], rtrim(rock_type) AS [rock_type],
rtrim(alt_type) AS [alt_type], rtrim(min_style) AS [min_style]
FROM survey sy
INNER JOIN tyrctl tl
ON sy.survey_date >= tl.fmdate
AND sy.survey_date

StackExchange Database Administrators Q#120804, answer score: 6