PostgreSQL Subquery with Examples

In this PostgreSQL tutorial, we will study the usage of the PostgreSQL subquery that will allow us to create queries within the query. And we are going to discuss the following list of topics.

• PostgreSQL subquery
• PostgreSQL subquery in select
• PostgreSQL subquery return multiple columns
• PostgreSQL subquery as column
• PostgreSQL subquery alias
• PostgreSQL subquery vs join
• PostgreSQL subquery performance
• PostgreSQL subquery join
• PostgreSQL subquery to array
• PostgreSQL subquery to json array

PostgreSQL subquery

In PostgreSQL, A query is a question or a request for information expressed. A subquery is a query within a PostgreSQL query. It can also be called a nested query or inner query.

Let’s understand this with the help of an example.

Suppose we want to find the cars whose rental rate is higher than the average rental rate. We can do it in a couple of steps.

• Firstly, we will find the average rental rate using the SELECT statement and the average function ( AVG).
• Secondly, we will use the outcome of the first query in the second SELECT statement to find the cars we want. The next query will get the average rental rate for cars. The below query gets the average rent rate.

SELECT AVG(PRICE) FROM MOTOR;

To build up a subquery, we will put the second query in brackets and use it in the WHERE clause as an expression. Now, we can get cars whose rent is higher than the average rent. Let’s check the query.

SELECT carno, carname, price FROM motor WHERE price > 60000.000000000000;

Let’s check the output of the above query.

Let’s understand with one more example, here we are going to use the us_cities table that contains the population, density, etc of the United States.

SELECT city FROM us_cities WHERE population < (SELECT avg(population) FROM us_cities);

Here, we are selecting the city of the United States whose population is less than the average population of all the cities of the United States using the subquery.

Read PostgreSQL Update Join

PostgreSQL subquery in select

We need a method to pass the answer of the first query to the second query in one query. The result is to make use of a subquery.

A subquery is a query that is fixed inside a SELECT, INSERT, UPDATE and DELETE statement, or inside one more subquery.

A subquery can make use of anywhere an expression is authorized. In this tutorial, we will target the SELECT statement only.

To build up a subquery, we will put the second query in brackets and use it in the WHERE clause as an expression.

SELECT carno, carname, price FROM motor WHERE price> ( SELECT AVG (price) FROM motor );

The query inside the brackets is called a subquery or an inner query. The query which holds the subquery is known as an outer query.

PostgreSQL implements the query that has a subquery. Firstly it will perform the subquery. Then, it will get the result and passes it to the outer query.

After that, implement the outer query. Let’s see the output for the above query.

Read Postgresql Add Foreign Key

Postgresql subquery return multiple columns

Postgresql subqueries that return multiple can columns are applied with the ALL, IN, ANY, or SOME operators.

We can likewise nullify conditions like NOT IN. A subquery that recommends one or more columns from its containing PostgreSQL statement is termed as a correlated subquery.

Dissimilar to non-correlated subqueries that are implemented precisely once before the implementation of a containing statement, a subquery is implemented once for each column in the transitional outcome set of the containing query.

Now, here is the statement that will show the syntax.

SELECT column1,column2,..
FROM table 1 outer
WHERE column1 operator( SELECT column1 from table 2 WHERE  column2=outer.column4)

Let’s check the query for the above syntax.

SELECT id,name,age,address,salary
FROM MNC 
WHERE salary >
(SELECT AVG(salary)
FROM MNC
WHERE id=id);

Let’s check the output of the above query.

Read Postgresql Joins

Postgresql subquery as column

A subquery that will redirect at one or more columns from its containing PostgreSQL statement is known as a correlated subquery.

Whereas non-correlated subqueries that are executed exactly once earlier than the execution of a containing statement, a correlated subquery is executed once for every candidate row within the correlated outcome set of the containing query. The below syntax will represent the syntax of a correlated subquery.

SELECT column1,column2,..
FROM column 1 >
WHERE column1 operator( SELECT column1 from table 2 WHERE column 1= column2)

The PostgreSQL runs pass the value of column2 from the outer table to the inner query and is compared to column2 of table 2.

Therefore, column1 will be taken from table 2, and depending on the operator it will be compared to column1 of the outer table.

If the expression turned out to be true, the row will be passed or else, it will not appear in the output.

But with the correlated queries, we might see some performance issues. Because for every record of the outer query, the correlated subquery is executed.

The execution is completely dependent on the data involved. But, in order to make sure that the query works efficiently, we can use some temporary tables.

Here is the output of the above code.

SELECT name,salary FROM MNC
WHERE salary >
(SELECT max(salary) FROM MNC
WHERE name='Smith');

Read PostgreSQL Min With Examples

Postgresql subquery alias

In this section, we will understand the working of PostgreSQL subquery Alias. Basically, when a short name is given to a table or a column by giving another name temporarily which is called a PostgreSQL subquery alias.

Table aliases assign the table new names for a limited period of time during the execution of a query.

Renaming is a temporary change in which the actual table name doesn’t change in the database. The column aliases are used to rename a table’s columns in a particular PostgreSQL query. The following query shows the syntax of a table alias.

In the below syntax, the table_name is assigned an alias as alias_name. Similar to column aliases, the AS keyword is optional. It means that we neglect the AS keyword like this. Let’s check the syntax.

table_name AS alias_name;

For example, the following query will produce this error.

SELECT * FROM (SELECT uid, COUNT(*) AS count FROM students GROUP BY 1 ORDER BY 2) LIMIT 1;

Because the correct fix is to give the subquery in the FROM clause an alias which is a name so that it can be uniquely identified in the query. We will do this even if we are not going to reference the name anywhere, like this.

SELECT * FROM (SELECT stu_id, COUNT(*) AS count FROM students GROUP BY 1 ORDER BY 2) AS children LIMIT 1;

This error is generally happening in development or test environments because queries written in this way will always error out when run, independent of the query plan or values passed in.

We should always add an alias name of our choice to the subquery in the PostgreSQL that’s generated by our application. Let’s check the output of the above queries.

Read PostgreSQL group by

Postgresql subquery vs join

The PostgreSQL Joins are used to merge records from at least two tables in a database. A JOIN is a means for uniting fields from two tables by using values common to each.

A join will be performed at whatever point different tables show up in the FROM condition of the inquiry.

A Subquery or Inner query and Nested query is a query inside PostgreSQL query and embedded inside the WHERE clause. Subqueries are applied in complex PostgresqlSQL queries.

Generally, there is a main outer query and at least one subqueries nested inside the outer query. Subqueries are usually basic or associated.

Those subqueries do not rely on the columns in the outer query, though correlated subqueries refer to data from the outer query.

Basically, Joins and subqueries are both used to merge data from different tables into one result. They share numerous likenesses and contrasts.

A subquery is used to return either a single value or a row set and joins are used to return rows.

A typical use for a subquery might be to count the usage of a summary value in a query. A subquery is easier to note but, a join is better improved by the server. For example, a Left Outer join generally works quicker because servers optimize it.

Subqueries and JOINS, one and the other will be utilized in a complex query to select data from various tables, but they do so in several ways. Sometimes, we have the option of either-even if there are cases in which a subquery is the only genuine choice.

Joins are commonly used in the FROM clause of the WHERE statement. But, we will find subqueries used in most clauses such as the:

• SELECT List – These subqueries generally return single values.
• WHERE clause– It depends upon the conditional operator we will see the single value or row-based subqueries.
• FROM clause– It is common to see row-based result subqueries used.
• HAVING clause – We mostly see subqueries returning single values in this situation.

Let’s check the example of Joins. Firstly we will create two tables. Here, we will be creating and inserting two different tables where we will perform actions on several types of joins.

In the example shown below, we will use the CREATE command to create two tables i.e. Best_motorcycle and Sports_motorcycle.

Let’s check the output for joins.

select * from Best_motorcycle;
select * from Sports_motorcycle;

Let’s have a look at the example of the subquery. Let’s check the code.

SELECT carno, carname, price FROM motor WHERE price> ( SELECT AVG (price) FROM motor );

Let’s check the output.

Read PostgreSQL add primary key

PostgreSQL subquery performance

Now we will learn about subquery performance. And now we will see how an original idea for a subquery would perform.

We can improve performance by using subquery clauses that affect the number or order of the rows in the subquery.

However, we can place a join with a subquery. Some subqueries can be transformed to joins for compatibility with older versions of Postgresql that don’t support subqueries. We can also move clauses from outside to inside the subquery.

Below is the subquery code.

select count(weather.*) from weather 
    where temp_hi not in (select temp_hi from weather);

Let’s check the output of the above code.

Read Postgresql Having Clause

PostgreSQL subquery join

Now we will learn how we use Postgresql subquery in the select clause performing JOIN with GROUP BY in a subquery. We have used INNER JOIN in the example explained.

Firstly we will create a table and then will insert the values into it. After that, we will implement the query for subquery join. Let’s check the queries below.

CREATE TABLE OBJECT (
    N INTEGER NOT NULL,
    S TEXT NOT NULL
);

INSERT INTO OBJECT
  SELECT
    (random()*1000000)::integer AS n,
    md5(random()::text) AS s
  FROM
    generate_series(1,1000000);

CREATE INDEX N_INDEX ON OBJECT(N);

SELECT 
    I.*
FROM OBJECT I
INNER JOIN (
    SELECT 
        COUNT(1), n
    FROM OBJECT
    GROUP BY N
) I2 ON I2.N = I.N
WHERE I.N IN (243477, 997947);

Let’s check the output for the above queries.

Read Postgresql while loop

PostgreSQL subquery to array

An Array is a group of adjacent memory locations which plays a significant role in PostgreSQL. Each data type has its companion array type e.g. integer has an integer array type, a character has a character array type, etc.

Let us suppose we characterize our data type PostgreSQL creates a corresponding array type in the background.

PostgreSQL permits us to explain a column to be an array of any accurate data type as well as a built-in type, a user-defined type, or an enumerated type.

Since multidimensional arrays are rectangular, inner constructors at the same level produce sub-arrays of similar dimensions.

Multidimensional array constructor elements can be anything submitting an array of the proper kind, not only a sub-ARRAY construct.

Constructing an array from the results of a subquery is also feasible. In this form, the array constructor is written with the keyword ARRAY with a parenthesized (not bracketed) subquery.

The subquery must return a single column. The result of the 1D array will have an element for every row in the subquery result, with an element type matching that of the subquery’s output column.

The subscripts of an array value construct with ARRAY constantly begin with one. Let’s check the queries followed by creating the table.

CREATE TABLE sudo (f1 int[], f2 int[]);

INSERT INTO sudo VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM sudo;

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');

Let’s check the output of the above query.

Read Postgresql REGEXP_REPLACE Function

PostgreSQL subquery to json array

Basically, the JSON array describes an ordered list of values in Postgresql using the psql tool. It can store multiple values. It can store string, number, boolean or object in a JSON array.

Values are separated by commas in JSON array We will understand this by creating a table followed by the queries for json_agg, json_build_object, and subquery of the table created. Let’s check the queries.

create table hint (a int primary key, b text);

insert into hint values (1, 'value1');
insert into hint values (2, 'value2');
insert into hint values (3, 'value3');

Now we will apply the query for json_agg, json_object and row_to_json(row) from select * from hint.

SELECT json_agg(hint) FROM hint;

SELECT
    json_build_object(
        'a', json_agg(hint.a),
        'b', json_agg(hint.b)
    )
FROM hint;    

select row_to_json(row) from (select * from hint) row;

Let’s check the output of the above queries.

You may like the following PostgreSQL tutorials:

• Postgresql Sum
• PostgreSQL Loop Examples
• Postgresql if else
• Postgres date range
• Drop Database PSQL
• PostgreSQL Rename Column

In this PostgreSQL tutorial, we have studied the usage of the PostgreSQL subquery that will allow us to create queries within the query. And we have discussed the following list of topics.

• PostgreSQL subquery
• PostgreSQL subquery in select
• PostgreSQL subquery return multiple columns
• PostgreSQL subquery as column
• PostgreSQL subquery alias
• PostgreSQL subquery vs join
• PostgreSQL subquery performance
• PostgreSQL subquery join
• PostgreSQL subquery to array
• PostgreSQL subquery to json array