Question 189959 in PostgreSQL Question posted in
The official documentation can be found here.

Inner Join Help on PostgreSQL

I have the following database relationship and query for PostgreSQL below:

enter image description here

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: enter image description here

TABLE Gender: enter image description here

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:

enter image description here

Tags:

2

Answers


  1. 0

    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:

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

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search