是否可以在任何地方使用JOINS来替换SQL中的子查询(Is it true that JOINS can be used everywhere to replace Subqueries in SQL)

我听说有人说表连接可以在任何地方用来替换子查询。 我在查询中对其进行了测试，但发现只有在使用子查询时才会检索到相应的数据集。 我无法使用连接获得相同的数据集。 我不确定我发现的是对的，因为我是RDBMS的新手，因此没有那么多经验。 我将尝试绘制我正在尝试的数据库的模式（用文字表示）：

该数据库有两个表：

Users （ ID ，姓名，城市）和友谊（ ID ， Friend_ID ）

Goal ：用户表用于存储简单的用户数据，友谊表用于表示用户之间的友谊。 友谊表将列作为外键，引用Users.ID。 表之间有多对多的关系。

问题：我必须检索所有用户的Users.ID和Users.Name，这些用户不是特定用户x的朋友，而是来自同一个城市（很像fb的朋友建议系统）。

通过使用子查询，我能够实现这一点。 查询看起来像：

SELECT ID, NAME FROM USERS AS U WHERE U.ID NOT IN (SELECT FRIENDS_ID FROM FRIENDSHIP, USERS WHERE USERS.ID = FRIENDSHIP.ID AND USERS.ID = x) AND U.ID != x AND CITY LIKE '% A_CITY%';

示例条目：

Users

Id = 1姓名= Jon City =孟买

Id = 2 Name = Doe City =孟买

Id = 3姓名= Arun City =孟买

Id = 4姓名= Prakash City =德里

Friendship

Id = 1 Friends_Id = 2

Id = 2 Friends_Id = 1

Id = 2 Friends_Id = 3

Id = 3 Friends_Id = 2

我是否可以通过执行连接在单个查询中获取相同的数据集。 怎么样？ 如果我的问题不明确，请告诉我。 谢谢。

注意：我通过指定两个表在子查询中使用了内连接：友谊，用户。 省略Users表并从外部使用U会产生错误（但如果不使用表的用户别名，查询在语法上就可以了，但此查询的结果包括ID和用户名，他们有多个朋友，包括用户有ID x。有趣，但不是问题的主题）。

I heard people saying that table joins can be used everywhere to replace sub-queries. I tested it in my query, but found that appropriate data set was only retrieved when I used sub-queries. I was not able to get same data set using joins. I am not sure if what I found is right because I am a newcomer in RDBMS, thus not so much experienced. I will try to draw the schema (in words) of the database in which I was experimenting:

The database has two tables:

Users (ID, Name, City) and Friendship (ID, Friend_ID)

Goal: Users table is designed to store simple user data and Friendship table represents Friendship between users. Friendship table has both the columns as foreign keys, referencing to Users.ID. Tables have many-to-many relationship between them.

Question: I have to retrieve Users.ID and Users.Name of all the Users, which are not friends with a particular user x, but are from same city (much like fb's friend suggestion system).

By using subquery, I am able to achieve this. Query looks like:

SELECT ID, NAME FROM USERS AS U WHERE U.ID NOT IN (SELECT FRIENDS_ID FROM FRIENDSHIP, USERS WHERE USERS.ID = FRIENDSHIP.ID AND USERS.ID = x) AND U.ID != x AND CITY LIKE '% A_CITY%';

Example entries:

Users

Id = 1 Name = Jon City = Mumbai

Id=2 Name=Doe City=Mumbai

Id=3 Name=Arun City=Mumbai

Id=4 Name=Prakash City=Delhi

Friendship

Id= 1 Friends_Id = 2

Id = 2 Friends_Id=1

Id = 2 Friends_Id = 3

Id = 3 Friends_Id = 2

Can I get the same data set in a single query by performing joins. How? Please let me know if my question is not clear. Thanks.

Note: I used inner join in the sub-query by specifying both tables: Friendship, Users. Omitting the Users table and using the U from outside, gives an error (But if not using alias for the table Users, query becomes syntactically okay but result from this query includes ID's and names of users, who have more than one friends, including the user having ID x. Interesting, but is not the topic of the question).

最满意答案

对于not in你可以使用left join和检查为is null ：

select u.id, u.name from Users u left join Friends f on u.id = f.id and f.friend_id = @person where u.city like '%city%' and f.friend_id is null and u.id <> @person;

在某些情况下，您只能通过内部/左/右连接来解决问题，但您的情况不是其中之一。

请检查sql小提琴： http ：//sqlfiddle.com/#！9/1c5b1 / 14

还有关于您的注意事项：根据您使用的引擎，您可以通过lateral连接或cross apply来实现您尝试做的事情。

For not in you can use left join and check for is null:

select u.id, u.name from Users u left join Friends f on u.id = f.id and f.friend_id = @person where u.city like '%city%' and f.friend_id is null and u.id <> @person;

There are some cases where you can't work out your way with just inner/left/right joins, but your case is not one of them.

Please check sql fiddle: http://sqlfiddle.com/#!9/1c5b1/14

Also about your note: What you tried to do can be achieved with lateral join or cross apply depending on the engine you are using.

更多推荐