SQL JOIN查询编写 [英] SQL JOIN query writing

问题描述

我正在尝试编写一个涉及两个表的简单查询. 人"表具有唯一的person_id和name，而朋友"表具有person_id和friend_id，这是对人表中person_id的FK.

I'm trying to write a simple query involving two tables. The "person" table has a unique person_id and a name, and the "friends" table has a person_id and a friend_id which is a FK to a person_id in the person table.

person:
<PK> int person_id 
varchar[45] name

friends: 
<PK> int person_id
<PK> int friend_id

我想选择第一个人的所有朋友的名字.

I want to select the name of all of person 1's friends.

我可以使用IN语句轻松做到这一点:

I can do this easily using an IN statement:

SELECT p.name FROM person p WHERE p.person_id IN (SELECT f.friend_id FROM friends f WHERE f.person_id = 1);

但是，我不擅长编写JOIN语句.有人可以帮我写等效的联接吗?

However, I am not proficient at writing JOIN statements. Can somebody help me write the equivalent join?

很明显，这是一个人为的示例，但是我尝试使用我的真实数据，并且从概念上讲缺少了一些东西.谢谢.

Clearly this is a contrived example, but I have tried with my real data and am conceptually missing something. Thanks.

推荐答案

您想要这样的东西:

SELECT p.name, f.friend_id
FROM person AS p
INNER JOIN friends AS f ON p.person_id = f.person_id
WHERE p.person_id = 1

这使用p.person_id = f.person_id

如果一个人没有朋友，则不会返回任何行-如果您不希望这样做，请使用LEFT JOIN，并且会得到一行带有NULL friend_id的行.

If a person has no friends, you won't get any rows back - if you don't want this then use LEFT JOIN and you'll get one row with a NULL friend_id.

如果您想将朋友重新加入社交圈，

if you want to join friends back on to person:

SELECT p.name AS person_name, friend.name AS friend_name
FROM person AS p                                         -- Our person
INNER JOIN friends AS f ON p.person_id = f.person_id     -- the join table
INNER JOIN person AS friend on f.friend_id = friend.id   -- Join back on person again
WHERE p.person_id = 1

对于您的应用，也许您需要像这样的三向联接，但更通常的是，您只需要如上所述的两向联接，或者像这样:

Maybe you need a 3-way join like this for your app, but more usually you'd only need a 2-way as above, or like this:

SELECT p.name, f.friend_id
FROM person AS p
INNER JOIN friends AS f ON p.person_id = f.friend_id
WHERE f.person_id = 1

这将为您提供与person_id 1成为朋友的所有人的姓名(而不是person_id 1的姓名)

This will give you the names of all the people that are friends with person_id 1 (but not person_id 1's name)

这篇关于SQL JOIN查询编写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！