Inner Join Help on PostgreSQL

Question

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:

There are two parts, first of do an normal inner join. Then use the Case statement in the selection part https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-case/ — Dijkgraaf, Jun 19 '23 at 02:13
[So do you have your answer?](https://meta.stackexchange.com/a/5235/169168) — Erwin Brandstetter, Jun 24 '23 at 03:55

Erwin Brandstetter · Answer 1 · 2023-06-19T21:20:06.813

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

fiddle

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:

How to concatenate columns in a Postgres SELECT?

Aside: you do not want to use the data type char(n). See:

Any downsides of using data type "text" for storing strings?

score 0 · Answer 2 · answered Jun 19 '23 at 21:31

0

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:

https://dbfiddle.uk/YBzEtnOv

answered Jun 19 '23 at 21:31

Joel Coehoorn

399,467
113
570
794

Inner Join Help on PostgreSQL

2 Answers2