/ ID: 804 Title: Using Nested Select in Postgres SQL 804 Meta Description: In this tutorial we learn how to use nested select in Postgres SQL. Code samples included. Meta Keywords: Nested Select Postgres SQL Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: Nested Select, PostgreSQL, SQL, Subquery, Subqueries, Insert Into, Not in Status: Published /
In this article, we'll explore how to use nested select in Postgres SQL. During this tutorial, we'll use the following structure:
Nested select statements are otherwise known as "subqueries".
Sometimes in our Postgres-based application we may need to pull data from a subset of data we create on the fly, update a table based on a subset of data, or insert to a table based on a data subset. Later in this article, we'll play with some whys and ways of doing this.
First, let's study the syntax of this powerful SQL tool, from a few perspectives:
Syntax 1: Subqueries in WHERE
SQL
SELECT
column_1
, column_2
, column_3
FROM
tbl_data
WHERE
column_1 IN -- this can also be "NOT IN", "EXISTS, an operator like "=", "<", and others.
(
SELECT
column_1
FROM
tbl_data
WHERE
[condition]
)
ORDER BY column_1
Syntax 1 Analysis
In the syntax example above, we are looking at two data sets that are both pulled from the same table, "tbl_data". The high level view is that the "inner" nested data set, the one in parentheses, runs first and the "outer" query is filtered based on the results of that inner, nested one returns. The IN you see here is important. It's telling PostgreSQL, "Only pull records from tbl_data where the value in column_1 exists in the recordset returned by the nested select query. We'll later see some ways this can be useful.
Syntax 2: Subqueries in FROM
SQL
SELECT
column_1
, column_2
, column_3
, nested.column_4
FROM
(
SELECT
column_4
FROM
tbl_data
WHERE
[condition]
) AS nested
Syntax 2 Analysis
In this syntax example, we are giving a name to our "inner" nested select query, "nested", so that we can refer to it in our outer select at the top of the overall SQL. Note that again, we are enclosing the nested select query in parenthesis. This is a must.
Before we do a real world example, let's see the syntax for using nested select statements with INSERT INTO.
Syntax 3: Subquery with INSERT INTO
SQL
INSERT INTO
tbl_data
(
column_1
, column_2
)
VALUES
(
SELECT
column_3
, column_4
FROM
tbl_other
WHERE
[condition]
)
Syntax 3 Analysis
In the INSERT INTO above, we begin by telling Postgres to add rows to "tbl_data". Next, we are determining which columns (column_1 and column_2) we want to fill with the two respective VALUES returned by the nested SELECT statement that follows and is encapsulated in parentheses. Finally, note that the nested select pulls its data from "tbl_other".
Here's a real world situation. If a developer didn't know about nested selects, in some situations they might believe they need to pull data from a table into an array, manipulate that array in some way (perhaps sorting), and then use that array to add rows into their first table. Fortunately, nested selects (subqueries) provide us with a far more efficient way of accomplishing the task. We'll dive in by writing the SQL, which will be structurally similar to "Syntax 3" above.
First, let's set up two tables with test data:
tbl_technologies_used
| t_name_tech | t_category_tech | i_rating | |---------------|-----------------|---------:| | Javascript | Language | 95 | | PostgreSQL | SQL database | 90 | | Python | Language | 90 | | MS SQL Server | SQL database | 90 | | C# | Language | 92 |
The above table is the table used by the company to track which technologies are being used by the company. The table below is filled with potential technologies. It's important to notice the bottom table includes tech that is already in the top table. So when we are building our INSERT INTO query using a NESTED SELECT, we want to be sure to keep duplicates out. This is a use case where nested selects excel.
tbl_technologies_proposed
| t_name_tech | t_category_tech | i_rating | |---------------|-----------------|---------:| | Javascript | Language | 95 | | Mongo | NoSQL database | 85 | | MySQL | SQL database | 50 | | PostgreSQL | SQL database | 90 | | Python | Language | 90 | | PHP | Language | 70 | | Java | Language | 75 | | MS SQL Server | SQL database | 90 | | C# | Language | 92 | | C++ | Language | 88 | | dBase | Flat database | 25 |
Writing the PostgreSQL query
SQL
INSERT INTO
tbl_technologies_used
(
t_name_tech
, t_category_tech
, i_rating
)
VALUES
(
SELECT
t_name_tech
, t_category_tech
, i_rating
FROM
tbl_technologies_proposed
WHERE
i_rating > 75
AND
t_name_tech NOT IN
(
SELECT
t_name_tech
FROM
tbl_technologies_used
)
)
Analysis
The first thing you may notice with our example above is that we have a nested SELECT in another nested SELECT! Why did we do this? Because we want to be sure we do not add a row to tbl_technologies_used that is already in that table. Let's take it line by line:
After we run that query, we get the following resulting dataset:
tbl_technologies_used
| t_name_tech | t_category_tech | i_rating | |---------------|-----------------|---------:| | Javascript | Language | 95 | | PostgreSQL | SQL database | 90 | | Python | Language | 90 | | MS SQL Server | SQL database | 90 | | C# | Language | 92 | | C++ | Language | 88 |
Here in this article we learned here how to use NESTED SELECT in order to query Postgres with SQL that will accomplish various tasks that require subsorting of data. These are also known as subqueries. We also created a real world example to help round out your understanding of how nest selects in your use of SQL in PostgreSQL database work.