How to Combine Data From Multiple Tables Using an SQL Joins

1. Introduction

In the world of data analytics, combining multiple tables in a database helps us correlate data and gain quick insights. In this tutorial, let’s utilize JOIN operations such as INNER JOIN and ON clauses to retrieve data from two tables.

2. Representative Data Sample

Let’s refer to the sample Department table, which lists University departments such as Computer Science and Civil Engineering, along with their corresponding identifiers id, names, and codes.

Additionally, we use the SELECT query to retrieve and display all rows from the Department table:

> SELECT * FROM Department;
 id |              name              | code
----+--------------------------------+------
  1 | Computer Science               | CS
  2 | Electronics and Communications | EC
  3 | Mechanical Engineering         | ME
  4 | Civil Engineering              | CE
  5 | Mathematics                    | MA
(5 rows)

Similarly, let’s consider the Faculty table, which includes faculty members in departments like Computer Science and Mathematics, detailing their roles and employment statuses.

In this context, the SELECT query enables us to display columns containing faculty details such as id, name, national_id, position, and more:

> SELECT * FROM Faculty;
 id  |      name      | national_id |      position       | start_date |  end_date  | department_id | active
-----+----------------+-------------+---------------------+------------+------------+---------------+--------
   1 | Anubha Gupta   |  1018901231 | Professor           | 2010-01-11 | 2027-03-11 |             2 | t
 111 | AV Subramanium |  1340902317 | Assistant Professor | 2011-05-11 |            |             1 | t
 121 | Risa Sodi      |  1409239017 | Associate Professor | 2010-01-11 |            |             1 | t
 512 | Casper Jones   |  4253513301 | Teaching Assistant  | 2021-04-11 |            |             3 | t
 601 | Sussie Smith   |  1657230918 | Professor           | 2019-01-11 | 2027-02-18 |             5 | t
 740 | Kira Wass      |  2314623876 | Teaching Assistant  | 2021-09-11 |            |             4 | t
 741 | Sophia Ker     |  2314437876 | Teaching Assistant  | 2022-08-11 |            |             4 | t
(7 rows)

Subsequently, the below query focuses on essential details like faculty names, their respective positions, and the departments they belong to. Furthermore, utilizing aliases like fac simplifies the referencing of the Faculty table throughout the query:

> SELECT fac.name, fac.position, fac.department_id FROM Faculty fac;
      name      |      position       | department_id
----------------+---------------------+---------------
 Anubha Gupta   | Professor           |             2
 AV Subramanium | Assistant Professor |             1
 Risa Sodi      | Associate Professor |             1
 Casper Jones   | Teaching Assistant  |             3
 Sussie Smith   | Professor           |             5
 Kira Wass      | Teaching Assistant  |             4
 Sophia Ker     | Teaching Assistant  |             4
(7 rows)

Consequently, the output provides a concise view of faculty information crucial for tasks using aliases.

3. Using INNER JOIN to Combine Data

Generally, the INNER JOIN facilitates the merging of data from related tables, allowing us to retrieve records that share matching values in both tables. In our example, we’ll merge data from the Department and Faculty tables.

Initially, it uses an INNER JOIN to link records where the faculty’s department ID matches the department’s ID. Subsequently, the SELECT statement specifies columns like department ID, faculty name, position, department name, and code.

Furthermore, the ORDER BY clause arranges results in ascending order based on faculty department ID:

> SELECT fac.department_id, fac.name, fac.position, dep.name, dep.code 
    FROM Department dep 
    INNER JOIN Faculty fac 
    ON fac.department_id = dep.id 
    ORDER BY fac.department_id ASC;
 department_id |      name      |      position       |              name              | code
---------------+----------------+---------------------+--------------------------------+------
             1 | AV Subramanium | Assistant Professor | Computer Science               | CS
             1 | Risa Sodi      | Associate Professor | Computer Science               | CS
             2 | Anubha Gupta   | Professor           | Electronics and Communications | EC
             3 | Casper Jones   | Teaching Assistant  | Mechanical Engineering         | ME
             4 | Kira Wass      | Teaching Assistant  | Civil Engineering              | CE
             4 | Sophia Ker     | Teaching Assistant  | Civil Engineering              | CE
             5 | Sussie Smith   | Professor           | Mathematics                    | MA
(7 rows)

Now, let’s further filter the results using the WHERE clause to include only faculty members and their departments from Computer Science or Civil Engineering:

> SELECT fac.name, fac.position, dep.name, dep.code 
    FROM Department dep 
    INNER JOIN Faculty fac 
    ON fac.department_id = dep.id 
    WHERE dep.name = 'Computer Science' OR dep.name = 'Civil Engineering';
      name      |      position       |       name        | code
----------------+---------------------+-------------------+------
 AV Subramanium | Assistant Professor | Computer Science  | CS
 Risa Sodi      | Associate Professor | Computer Science  | CS
 Kira Wass      | Teaching Assistant  | Civil Engineering | CE
 Sophia Ker     | Teaching Assistant  | Civil Engineering | CE
(4 rows)

4. Conclusion

In summary, mastering SQL’s JOIN operations enables comprehensive data retrieval across interconnected tables, fostering deeper insights for data analysis. By leveraging examples such as merging faculty details with department information, this article equips us with essential skills to manage and extract valuable information from complex datasets.