我们如何使用旧语法进行LEFT JOIN? [英] How do we do LEFT JOIN with old syntax?

问题描述

我们如何使用旧语法进行LEFT JOIN?

How do we do LEFT JOIN with old syntax?

假设您有一个User表和UserRole表，并且您在User表中保存了UserRole的ID.

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

这是旧的符号:

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

现在，让我们假设所有用户都不具有角色，UserRoleId为0或NULL.

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

问题是:在不使用单词JOIN的情况下，如何使用旧语法进行同样的操作?

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

推荐答案

运算符是*=和=*(取决于每列谓词的哪一侧):

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

但是，自SQL Server 2012起不推荐使用这些方法，因为从那时起，对于24年前终止的联接语法就没有向后兼容性. 我不知道您为什么要使用此功能，但这是一些使您从阴暗面退回的原因:

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:

坏习惯:使用老式的JOIN

或者，如果您希望使用一种没有联接或专有语法的替代方法，则可以使用:

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);

但是，再次麻烦一下，ANSI 92中引入了LEFT JOIN语法，如果您不能在数据库中使用它，那么该是更改数据库供应商的时候了，而不是您的语法.

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.

这篇关于我们如何使用旧语法进行LEFT JOIN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！