Retrieving Data From Multiple Tables With SQL Queries

1. Overview

In SQL, querying data from multiple tables is a fundamental operation that enables more complex and informative data retrieval. This can be achieved primarily through two approaches: using JOIN or via subqueries. Each method has its advantages and is suitable for different scenarios.

In this tutorial, we’ll explore both techniques with respective practical example use cases to illustrate their applications.

Notably, we perform all operations in the Postgres database management system (DBMS) using the Department and Faculty table from the Baeldung University database schema. However, the same process applies to other SQL systems as well.

2. Using JOIN

Joining tables is a fundamental SQL operation that combines rows from two or more tables based on related columns. Let’s explore the most common types of JOIN with practical examples for retrieving data from multiple tables.

2.1. INNER JOIN

An INNER JOIN retrieves rows from both tables that meet the join condition. It returns only the rows where there is a match in both tables.

For example, let’s retrieve the names of faculty members along with their department names and their positions:

SELECT Faculty.name AS faculty_name, Faculty.position, Department.name AS department_name
 FROM Faculty
 INNER JOIN Department ON Faculty.department_id = Department.id;
+-----------------+---------------------+--------------------------------+
| faculty_name    | position            | department_name                |
|-----------------+---------------------+--------------------------------|
| Anubha Gupta    | Professor           | Electronics and Communications |
| Anubha Gupta    | Associate Professor | Electronics and Communications |
| Anubha Gupta    | Assistant Professor | Electronics and Communications |
| Peter Pan       | Professor           | Electronics and Communications |
| Peter Pan       | Associate Professor | Electronics and Communications |
| Peter Pan       | Assistant Professor | Electronics and Communications |
| Nando de Fretas | Associate Professor | Electronics and Communications |
...
SELECT 72

This query returns only the positions and names of faculty members associated with a department.

2.2. LEFT JOIN

LEFT JOIN retrieves all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

For example, let’s retrieve the names, start dates, and respective department names of all faculty members, including those who are not assigned to any department.

First, let’s insert a faculty member without a department for the purpose of this demo:

INSERT INTO Faculty (id, name, national_id, position, start_date, department_id)
VALUES (4, 'John Doe', 1234567890, 'Lecturer', '2022-06-01', NULL);

Then, let’s perform the LEFT JOIN query:

SELECT 
     Faculty.name AS faculty_name, 
     Faculty.start_date, 
     Department.name AS department_name
 FROM 
     Faculty
 LEFT JOIN 
     Department ON Faculty.department_id = Department.id;
+-----------------+------------+--------------------------------+
| faculty_name    | start_date | department_name                |
|-----------------+------------+--------------------------------|
| Anubha Gupta    | 2010-01-11 | Electronics and Communications |
| Anubha Gupta    | 2007-01-09 | Electronics and Communications |
| Anubha Gupta    | 2004-05-11 | Electronics and Communications |
| Peter Pan       | 2007-05-11 | Electronics and Communications |
| Peter Pan       | 2004-07-11 | Electronics and Communications |
| Peter Pan       | 2001-05-11 | Electronics and Communications |
| Nando de Fretas | 2023-03-11 | Electronics and Communications |
...
| John Doe        | 2022-06-01 | <null>                         |
...
SELECT 73

This query includes all faculty members, with their department names if available. If a faculty member doesn’t belong to any department, the department name is NULL.

2.3. RIGHT JOIN

A RIGHT JOIN retrieves all rows from the right table and the matched rows from the left table. Just like the LEFT JOIN, if there is no match, the result is NULL on the side of the left table.

For example, let’s retrieve the codes and faculty member names of all departments:

SELECT Department.name AS department_name, Department.code, Faculty.name AS faculty_name
 FROM Faculty
 RIGHT JOIN Department ON Faculty.department_id = Department.id;
+--------------------------------+------+-----------------+
| department_name                | code | faculty_name    |
|--------------------------------+------+-----------------|
| Electronics and Communications | EC   | Anubha Gupta    |
| Electronics and Communications | EC   | Anubha Gupta    |
| Electronics and Communications | EC   | Anubha Gupta    |
| Electronics and Communications | EC   | Peter Pan       |
| Electronics and Communications | EC   | Peter Pan       |
| Electronics and Communications | EC   | Peter Pan       |
...
SELECT 72

This query includes all departments, with their faculty members if available.

2.4. FULL JOIN

A FULL JOIN retrieves all rows when there is a match in either the left or right table.

For example, let’s retrieve all faculty members and departments, even if there are no matches between them:

SELECT Faculty.name AS faculty_name, Faculty.position, Department.name AS department_name
 , Department.code
 FROM Faculty
 FULL JOIN Department ON Faculty.department_id = Department.id;
+-----------------+---------------------+--------------------------------+------+
| faculty_name    | position            | department_name                | code |
|-----------------+---------------------+--------------------------------+------|
| Anubha Gupta    | Professor           | Electronics and Communications | EC   |
| Anubha Gupta    | Associate Professor | Electronics and Communications | EC   |
| Anubha Gupta    | Assistant Professor | Electronics and Communications | EC   |
| Peter Pan       | Professor           | Electronics and Communications | EC   |
| Peter Pan       | Associate Professor | Electronics and Communications | EC   |
| Peter Pan       | Assistant Professor | Electronics and Communications | EC   |
| Nando de Fretas | Associate Professor | Electronics and Communications | EC   |
| Nando de Fretas | Assistant Professor | Electronics and Communications | EC   |
...
SELECT 72

This query includes all faculty members and all departments. If a faculty member doesn’t belong to any department, the department name and code are NULL. Similarly, if a department does not have any faculty members, the faculty name and position will be NULL.

3. Using Subqueries

Subqueries, also known as inner queries or nested queries, are used to perform operations in two steps. First, the subquery retrieves a set of data, and then the outer query uses this data to produce the final result.

Let’s consider some example use cases of subqueries to retrieve data from multiple tables.

3.1. Using Subqueries in the SELECT Clause

For example, let’s retrieve the names of faculty members and the names of their departments using a subquery:

SELECT 
     name,
     (SELECT name FROM Department WHERE id = Faculty.department_id) AS department_name
 FROM Faculty;
+-----------------+--------------------------------+
| name            | department_name                |
|-----------------+--------------------------------|
| Anubha Gupta    | Electronics and Communications |
| Anubha Gupta    | Electronics and Communications |
| Anubha Gupta    | Electronics and Communications |
| Peter Pan       | Electronics and Communications |
| Peter Pan       | Electronics and Communications |
| Peter Pan       | Electronics and Communications |
| Nando de Fretas | Electronics and Communications |
...

This query first executes the subquery to find the department name for each faculty member. Then, it returns the faculty names along with their respective department names.

3.2. Using Subquery in the WHERE Clause

We can also locate the subquery as a part of a condition.

For example, let’s retrieve the names of faculty members that belong to the Computer Science department:

SELECT name
 FROM Faculty
 WHERE department_id = (SELECT id FROM Department WHERE name = 'Computer Science');
+----------------+
| name           |
|----------------|
| AV Subramanium |
| Risa Sodi      |
| Risa Sodi      |
| Wlliam Liu     |
| Wlliam Liu     |
| Wlliam Liu     |
| Wlliam Liu     |
| Cormen Qiu     |
...
SELECT 20
Time: 0.016s

This query uses a subquery to find the department ID of Computer Science and then retrieves the names of faculty members who are part of that department.

3.3. Using Subquery in the FROM Clause

A subquery can form another set of data just like a new temporary table.

For example, let’s retrieve the average start date of faculty members in each department:

SELECT 
     department_name, 
     TO_CHAR(TO_TIMESTAMP(AVG(EXTRACT(EPOCH FROM start_date))), 'YYYY-MM-DD') AS average_start_date
 FROM (
     SELECT 
         Faculty.start_date, 
         Department.name AS department_name
     FROM Faculty
     INNER JOIN Department ON Faculty.department_id = Department.id
 ) AS department_faculty
 GROUP BY department_name;
+--------------------------------+--------------------+
| department_name                | average_start_date |
|--------------------------------+--------------------|
| Civil Engineering              | 2016-04-11         |
| Electronics and Communications | 2012-11-28         |
| Computer Science               | 2015-07-15         |
| Mechanical Engineering         | 2017-12-23         |
| Mathematics                    | 2019-03-07         |
+--------------------------------+--------------------+
SELECT 5
Time: 0.014s

This query first joins the Faculty and Department tables to get the start_date and department_name for each faculty member. Then, it groups the results by department_name and calculates the average start_date for each department, displaying the result in a readable date format

4. Conclusion

In this article, we’ve explored the powerful SQL techniques of joining and using subqueries to retrieve complex and informative data from multiple tables. Additionally, we’ve demonstrated various types of joins, including INNER, LEFT, RIGHT, and FULL JOIN, with practical examples to illustrate their distinct use cases.

Understanding these methods enhances our ability to efficiently query and analyze relational data, making them essential tools for any database professional.