/ 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 /

Introduction

In this article, we'll explore how to use nested select in Postgres SQL. During this tutorial, we'll use the following structure:

Prerequisites

What is nested select statement?

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".

How and why we use nested select statements

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 |

Tidbits

Conclusion

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.