How do we do LEFT JOIN with old syntax?

Question

How do we do LEFT JOIN with old syntax?

Let's say you have a User table and UserRole table, and you are holding the ID of the UserRole in User table.

Here is the query to retrieve all User's names, and the Role Names with the new notation:

SELECT U.Username, US.[Desc] FROM [User] U
INNER JOIN UserRole US ON U.UserRoleId = US.Id

And here is the old notation:

SELECT U.Username, US.[Desc] FROM [User] U, UserRole US
WHERE U.UserRoleId = US.Id

Now, let's assume that all users don't have a role, the UserRoleId is either 0 or NULL.

Here is the query to retrieve all User's names, and the Role Names with the old notation:

SELECT U.Username, US.[Desc] FROM [User] U
LEFT JOIN UserRole US ON U.UserRoleId = US.Id

Question is: How do we do the same with old syntax, without using the word JOIN?

Solution 1

The operators are *= and =* (depending on which side of the predicate each column is):

SELECT U.Username, US.[Desc] 
FROM [User] U, UserRole US
WHERE U.UserRoleId *= US.Id

These have been deprecated since SQL Server 2012 though, since then there is no backward compatibility for a join syntax that was discontinued 24 years ago. I have no idea why you might want to use this, but here are some reasons to sway you back from the dark side:

Bad habits to kick : using old-style JOINs

Or, if you want an alternative way without joins, or proprietary syntax you can use:

SELECT U.Username, US.[Desc] 
FROM [User] U, UserRole US 
WHERE U.UserRoleId = US.Id 
UNION ALL 
SELECT U.Username, NULL 
FROM [User] U 
WHERE NOT EXISTS (SELECT 1 FROM UserRole US WHERE U.UserRoleId = US.Id);

But once again, why bother, the LEFT JOIN syntax was introduced in ANSI 92, if you can't use it with your database, it is time to change your database vendor, and not your syntax.

Solution 2

Use the PLUS(+) sign :

SELECT U.Username, US.[Desc] FROM [User] U, UserRole US
WHERE U.UserRoleId = US.Id(+)

The + should be placed on any column of the right table of the LEFT JOIN that appears in the WHERE clause.

Though - this is not suggested, this form of syntax usually leads to errors due to the messy code it creates