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

Convert Inner Join to Subquery in PostgreSQL

I am joining two tables with the student table using the inner join below:

select (first_name || ' ' ||last_name) as "Student Name", gender, race from student
inner join gender on student.gender_id = gender.gender_id 
inner join race on student.race_id = race.race_id

I want to know how I can get the same result using a subquery instead of using inner join on PostgreSQL?

Below are my code for the three tables (Student, Gender, Race) that I have created:

create table Student(Student_ID int primary key, first_name varchar(30), last_name varchar(30), gender_id int, race_id int, constraint gender_id_fk foreign key(gender_id) references gender(gender_id), constraint race_id_fk foreign key(race_id) references race(race_id));
create table Gender(Gender_ID int primary key, Gender varchar(30));
create table Race(Race_ID int primary key, Race varchar(30));
insert into Gender values(1, 'Male');
insert into Gender values(2, 'Female');
insert into Gender values(3, 'Other');
insert into Race values(1, 'White');
insert into Race values(2, 'Black');
insert into Race values(3, 'Asian');
insert into Race values(4, 'Native American');
insert into Race values(5, 'Other');
insert into Student values(1, 'John', 'Smith', 1, 1);
insert into Student values(2, 'Sara', 'Lee', 2, 3);
insert into Student values(3, 'Sam', 'Crawford', 3, 5);
insert into Student values(4, 'Cindy', 'Williams', 2, 2);

Below is the ERD relationship diagram in case if you need to take a look:

ERD Diagram

**Desired Output using subquery below: **
Output from Subquery

How can I get the same result using a subquery instead of using inner join?

Tags:

2

Answers


  1. 0

    Heres the query without JOINS:

    SELECT
  (first_name || ' ' || last_name) AS "Student Name",
  (SELECT gender FROM gender WHERE gender_id = student.gender_id) AS gender,
  (SELECT race FROM race WHERE race_id = student.race_id) AS race
FROM
  student;

    Output:
    enter image description here

    Note: Its better to use JOINS, as they are faster as compared to SUBQUERIES for indexed data.

    Login or Signup to reply.
  2. 0

    Your original query is the most straight-forward way. Because what you are trying to achieve is to create a joint view of multiple tables fully stitched together using a common key, which is exactly what JOIN is for. Also, subquery is usually used when there are more layers to the question rather than a case of just fully stitching tables together.

    If you just don’t want to use INNER JOIN, but achieve the result as INNER JOIN, using a CROSS JOIN with WHERE clause does the job as well. But I think it is unnecessary.

    SELECT
  (s.first_name || ' ' ||s.last_name) AS Student_Name,
  g.Gender AS Gender,
  r.Race AS Race
FROM Student AS s, Gender AS g, Race AS r
WHERE g.Gender_ID = s.gender_id AND r.Race_ID = s.race_id;

    There are usually 4 types of subquery

    1. WITH: this is used when you’d like to pseudo-create a temporary
      table from an existing table and place it at the top of the larger
      query
    2. Nested: it is used when you’d like the temporary table to act as a
      filter within the larger query, and it often sits within the WHERE
      clause
    3. Inline: it is used in the same way as the WITH. Instead of the
      temporary table sitting on top of the larger query, it’s embedded
      within the FROM clause.
    4. Scalar: it is used when you want to generate a scalar value to be
      used as a benchmark

    None of above is really needed in this case.

    On a side note, looking at the ERD, you are indicating that student table has one-to-many relationship with gender table and race table, so one student can have multiple different genders or races? or should it be one race or gender can correspond to multiple students? or are you better off just combining all into one table, race and gender are attributes about a student, then you don’t even have to do a join? These are interesting questions to consider.

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

Back To Top
Search