T-SQL Left Join Symbol

Question

Hi, What is the symbol (like *=) for doing a left join? I've got table A and B, must always return all records from table A even if there is no records in table B.

Answer 1

You shouldn't be using that operator, as it was deprecated in Sql Server 2008, and will be removed in future versions.

You should use ANSI compliant LEFT JOIN or LEFT OUTER JOIN instead.

It was deprecated for a reason. That operator's syntax is confusing (it conflicts with many language's standard overload of "multiply and assign") and is non-standard.

Answer 2

This is the new ansi standard syntax, much clearer imo.

SELECT *
FROM A
LEFT OUTER JOIN B
ON A.ID = B.ID

Answer 3

Avoid any ancient syntax like that. Rewrite it to include the newer "LEFT OUTER JOIN" and/or "RIGHT OUTER JOIN" syntax:

SELECT
    a.*, B.*
    FROM TableA                 a
        LEFT OUTER JOIN TableB  b ON a.id=b.id

Difference between * = and LEFT Outer Join
from the link:

In earlier versions of Microsoft® SQL ServerT 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax

Answer 4

Besides of that, use of ANSI standard syntax LEFT [OUTER] JOIN for joins simplifies looking for errors a lot, believe me. It allows also for clearer distinction between filters in WHERE clause and join operands. I would recommend also use ANSI syntax for inner joins.