How to Use the PostgreSQL Complex Queries Part 1

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

The simple PostgreSQL Query is used to request data from a database. However, there are times when specific types of data, such as data from different tables, must be fetched from the database by using a longer, or complex, query. Here PostgreSQL complex queries are well suited to these specific situations. Part one of this multipart tutorial will explain how to execute PostgreSQL complex queries along with providing practical working examples.

Prerequisites

  • Because this tutorial will cover how to use PostgreSQL complex queries, PostgreSQL must be properly installed and configured on the local machine.
  • A basic working knowledge of PostgreSQL is needed in order to follow the instruction and examples in this tutorial.

Subqueries in PostgreSQL

Subqueries are used to retrieve data that will be used in a main query. Because it is a query within a main query, a subquery is also sometimes referred to as an “inner” or “nested” query.

Following is a sample syntax of commonly used subquery:

1
2
3
4
5
6
SELECT column_name
FROM TABLE_NAME
WHERE column_name OPERATOR
(SELECT column_name
FROM TABLE_NAME
[WHERE])

Here is an example using a table named “employees:”

1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name | age | department_name | salary
----+------------+-----------+-----+-----------------+--------
7 | Ella May | Stevens | 31 | Accounting | 5000
9 | Ayyan | Bourne | 30 | Accounting | 5000
1 | Lacie | Serrano | 31 | Accounting | 5000
2 | Marius | Bryne | 27 | Engineering | 7300
3 | Anita | Matthams | 28 | Engineering | 7300
10 | Manha | Ferry | 28 | Engineering | 7300
4 | Maryam | Cohen | 36 | Marketing | 5300
8 | Ruari | Ball | 26 | Marketing | 5300
5 | Ronald | Mcmillan | 32 | IT | 6000
6 | Payton | Glover | 35 | IT | 6000
(10 ROWS)

Here is an example of subquery that will select data from just one table:

1
2
3
4
5
6
7
8
9
SELECT * FROM employees
WHERE id IN ( SELECT id FROM employees WHERE salary >= '6000' );
id | first_name | last_name | age | department_name | salary
----+------------+-----------+-----+-----------------+--------
2 | Marius | Bryne | 27 | Engineering | 7300
3 | Anita | Matthams | 28 | Engineering | 7300
10 | Manha | Ferry | 28 | Engineering | 7300
5 | Ronald | Mcmillan | 32 | IT | 6000
6 | Payton | Glover | 35 | IT | 6000

And here is an example of subquery that will select data from two tables, with the second table being named “en_projects:”

1
2
3
4
5
6
id | e_id | number_of_projects
----+------+--------------------
1 | 2 | 6
2 | 3 | 9
3 | 10 | 5
(3 ROWS)

Note that the following example will also use the previously displayed employees table.

In this example, “Engineers” will be listed if the number of their projects is equal to or greater than six. Note that the column in question is located on the en_projects table, shown in the following query:

1
2
3
4
5
6
7
SELECT first_name, last_name FROM employees
WHERE id IN
(SELECT e_id FROM en_projects WHERE number_of_projects >= '6');
first_name | last_name
------------+-----------
Anita | Matthams
Marius | Bryne

Complex Queries Examples

This section will cover some of the more complex examples that may prove helpful when writing longer queries.

Here is one example using the employees table:

1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name | age | department_name | salary
----+------------+-----------+-----+-----------------+--------
7 | Ella May | Stevens | 31 | Accounting | 5000
9 | Ayyan | Bourne | 30 | Accounting | 5000
1 | Lacie | Serrano | 31 | Accounting | 5000
2 | Marius | Bryne | 27 | Engineering | 7300
3 | Anita | Matthams | 28 | Engineering | 7300
10 | Manha | Ferry | 28 | Engineering | 7300
4 | Maryam | Cohen | 36 | Marketing | 5300
8 | Ruari | Ball | 26 | Marketing | 5300
5 | Ronald | Mcmillan | 32 | IT | 6000
6 | Payton | Glover | 35 | IT | 6000
(10 ROWS)

Combining Two Columns

Combining two columns strings isn’t a very complex task when the CONCAT() function, used to concatenate two or more strings into one, is utilized.

Following is a sample using theCONCAT() function syntax:

1
SELECT CONCAT();

Following is an explanation of the CONCAT() function.

As shown in the previous table, the first and the last name of the employees are in different columns. The following command can be used to query the combined first and last names into one column:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT CONCAT(first_name, ' ', last_name) AS employees_name,
age, department_name
FROM employees;
employees_name | age | department_name
------------------+-----+-----------------
Ella May Stevens | 31 | Accounting
Ayyan Bourne | 30 | Accounting
Lacie Serrano | 31 | Accounting
Marius Bryne | 27 | Engineering
Anita Matthams | 28 | Engineering
Manha Ferry | 28 | Engineering
Maryam Cohen | 36 | Marketing
Ruari Ball | 26 | Marketing
Ronald Mcmillan | 32 | IT
Payton Glover | 35 | IT
(10 ROWS)

Note that the names have been successfully combined into one column.

Getting The Maximum Value

This last example will demonstrate how to obtain the highest and lowest values in the salary column as well as the gap between those values. Execute the following command to obtain theses values:

1
2
3
4
5
6
SELECT MAX(salary) AS highest, MIN(salary) AS lowest,
(MAX(salary) - MIN(salary)) AS gap FROM employees ;
highest | lowest | gap
---------+--------+------
7300 | 5000 | 2300
(1 ROW)

Conclusion

This was part one of a tutorial series explaining how to use PostgreSQL complex queries to request data from a database. The tutorial specifically explained subqueries in PostgreSQL and provided a sample syntax. The article then explained how to execute complex queries and provided some complex examples used in writing more complex queries, including the combining of two columns with the CONCAT() function. Finally, the tutorial explained how to get the maximum value of a column as well as the gap between those values. Remember that a subquery is used to retrieve data that will be included in a main query. Hopefully this tutorial has provided insight into executing PostgreSQL complex queries and provided the tools that will help in executing those queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.