英文:
Inner Join Help on PostgreSQL
问题
我有以下的数据库关系和针对 PostgreSQL 的查询：
创建性别表：
create table gender(
gender_id int primary key,
gender char(10))
创建学生表：
create table student(
student_id int primary key,
first_name char(50) not null,
last_name char(50) not null,
gender_id int not null,
constraint gender_fk foreign key(gender_id) references gender(gender_id))
插入性别数据：
insert into gender values(1, 'Male');
insert into gender values(2, 'Female');
insert into gender values(3, 'Other');
插入学生数据：
insert into student values(101, 'John', 'Smith', 1);
insert into student values(102, 'Sara', 'Bradford', 2);
insert into student values(103, 'Joseph', 'Brown', 1);
insert into student values(104, 'David', 'Lopez', 3);
如何创建一个内连接语句以检查学生的性别是否为 'Female'？
所有其他性别（'Male' 和 'Other'）将只有空值。我想要一个输出如下的内连接表：
SELECT s.student_id, s.first_name, s.last_name, g.gender
FROM student s
LEFT JOIN gender g ON s.gender_id = g.gender_id
WHERE g.gender = 'Female';
请注意，这个查询使用了左连接 (LEFT JOIN) 并且筛选出了性别为 'Female' 的学生。其他性别的学生将在性别列中具有空值。
英文:
I have the following database relationship and query for PostgreSQL below:
create table gender(
gender_id int primary key,
gender char(10))
create table student(
student_id int primary key,
first_name char(50) not null,
last_name char(50) not null,
gender_id int not null,
constraint gender_fk foreign key(gender_id) references gender(gender_id))
insert into gender values(1, 'Male');
insert into gender values(2, 'Female');
insert into gender values(3, 'Other');
insert into student values(101, 'John', 'Smith', 1);
insert into student values(102, 'Sara', 'Bradford', 2);
insert into student values(103, 'Joseph', 'Brown', 1);
insert into student values(104, 'David', 'Lopez', 3);
TABLE Student: 
TABLE Gender: 
How do I create an inner join statement that checks if the student's gender is 'Female'?
All other genders ('Male' and 'Other') will just have null value. I want an output like the following inner-joined table:
答案1
得分: 3
不需要涉及gender表：
SELECT student_id, first_name || ' ' || last_name AS full_name
, CASE WHEN gender_id = 2 THEN 'Female' END AS chk_female
FROM student;
SQL CASE 默认情况下返回 null，当缺少 ELSE 部分时。
如果坚持要进行连接操作，最好使用 LEFT [OUTER] JOIN 并在连接条件中筛选出 'Female'。然后，所有其他性别都会自动默认为 null：
SELECT s.student_id, s.first_name || ' ' || s.last_name AS full_name
, g.gender AS chk_female
FROM student s
LEFT JOIN gender g ON g.gender_id = s.gender_id AND g.gender = 'Female';
第二个查询适用于gender_id的值不容易知道的情况，或者'Female'实际上是一个变量。
由于first_name和last_name都是NOT NULL，因此普通的连接操作是可以的。否则，可以考虑使用concat_ws()进行空安全的连接。参考：
另外：您不应该使用数据类型 char(n)。参考：
英文:
We don't even need to involve the gender table:
SELECT student_id, first_name || ' ' || last_name AS full_name
, CASE WHEN gender_id = 2 THEN 'Female' END AS chk_female
FROM student;
SQL CASE defaults to null, when the ELSE part is missing.
If you insist on a join, rather use LEFT [OUTER] JOIN and filter for 'Female' in the join condition. Then all other genders default to null automatically.:
SELECT s.student_id, s.first_name || ' ' || s.last_name AS full_name
, g.gender AS chk_female
FROM student s
LEFT JOIN gender g ON g.gender_id = s.gender_id AND g.gender = 'Female';
This second query is for situations where the gender_id of 'Female' is not readily known already, or 'Female' is really a variable.
Since both first_name and last_name are NOT NULL, plain concatenation is fine. else consider null-safe concatenation with concat_ws(). See:
Aside: you do not want to use the data type char(n). See:
答案2
得分: 0
不需要使用 JOIN 进行这个操作，但如果你要使用的话，我建议使用 OUTER JOIN 而不是 INNER JOIN。请参考这个链接：
英文:
You don't need a JOIN for this, but if you were to use one I would suggest an OUTER join instead of INNER. See this:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论