PostgreSQL

Postgresql Subquery

A subquery (nested) is a part of the main query. This subpart is embedded with the “where” clause in the statement. A subquery is used will all possible “select,” Delete”, etc. statements with some basic operators like >,<, and,  =, etc. One thing that competes for this command from others is that it doesn’t support the use of the “ORDER BY” command inside the subquery because we use a subquery to make the query easier, whereas the use of this clause can make it more complex. However, we can use “order by” clause in the main command. The syntax of the subquery varies for every other statement. The examples used in this guide will be favorable in explaining the respective syntaxes.

Subquery and the Select Command

Consider a table named technology. The table is created by using the “create” and “insert” statements. The table has 4 attributes (column). Display the contents of the table using the select statement.

>> select * from technology;

We will apply a subquery to this table. This subquery is related to the use of the “select” statement. Subquery works in such a situation when you need to apply more than one condition on a single table. The main query selects some portion of the table, whereas the subquery selects the rest table. The concept will be clear after understanding the given an example.  The select statement itself is used in many ways in a subquery. All possible methods are explained here one by one. Let us start the examples.

>> select t1.name, t1.year, ( select min (id) from t2 where t1.id = t2.id) subquery1 from technology t1;

Explanation of this query required the concept of object creation. From the query, it is clear that the table’s column names are accessed through the reference letter, known as an object in most places. The first select command, the main command, fetches two columns names and the year through “t1”, whereas the 3rd column depends upon the subquery. Another select command is used to fetch the id of the technology table, whose name is set as “subquery1”. Here, a condition is applied; the ids are displayed in the resultant table if the condition is satisfied. Another view of the table is created that is fetched by “t2”. Firstly, the comparison is done between the ids of both t1 and t2. When both ids are the same, display them in the result with the other two columns. In other words, the whole column of id will be selected because the column is the same in both tables.

Moving towards the next “select” example, this example is simple as compared to the last one, as there is no use of any additional accessing object. As we know that first, the sub-query is executed by the system. In the sub-query, the condition is applied on a column of the table, where the model number should be greater than 3. When the system gets the resultant factors of the sub-query, the main query will implement it on itself. For instance, all other data of the columns will be displayed according to the resultant of the sub-query, respectively.

>> select * from technology where id in (select id from technology where models > 3);

You can see that a single row is removed from the table that was not fulfilling the condition. One thing you must have in your mind is the question that why we have used the sub-query here, the condition could be applied with the “where” clause directly in the select statement. The answer is that the sub-query divides the complex query into isolated parts. The accuracy measures increase nth times more in sub-query than using a single command.

We have now switched towards another table named “sample1”. The table has three columns; one is age. We will apply a simple select statement first to calculate the average age of the person because this average age will be used further in the table.

>> select * from sample1;

>> select AVG (age) from sample1;

AVG ( column name) is a built-in function of Postgres. Here, a subquery is used; the query will display people with an age greater than the average age calculated. The subquery will fetch the average age of the column age.

>> select * from sample1 where age > (select AVG (age) from sample1);

2 rows are selected as these ages were greater than the average age.

Subquery with the use of insert statement

Till now, we have used sub-query on a single table. But now, there is an example of using the main query on one table and the sub-query on the other one. For this purpose, we have used two tables here. One is software, and the other is a system. Fetch the record through the select statement for both tables.

>> select * from software;

Now we will apply the sub-query to the given tables.

Select statement as the main query can also be used in this case. But in this example, “insert” is used. This statement will add a row in one table concerning the value in the row of another table by using a “where” clause.

>> insert into software (id, name) select id, name from system where system.id > 5 AND system.name = ‘Windows’;

The “system” table values are inserted into the “software” table by matching both the tables’ id and names. Each column of the different table is specified through the table name. The subquery relies on the “select” statement here to fetch the record in two columns of the table.

Alter table with the subquery

Now consider another table name, “sample1”, having names, ids, and ages of people. And the other table is the same as introduced in the previous example “system”.

The update query is used to alter the table by replacing the previous one with the new one given in the command. Whereas in the case of the subquery, the value is not directly given. It is fetched from the subquery applied in the second table.

>> update sample1 set name = (select name from system where system.id = sample1.id);

This command “sample1” table is updated by selecting the name from the “system” table where both ids are the same. The resultant sample1 table shows the two rows. As id and the name were selected, so the age column remained intact.

Subquery and delete statement

A delete statement is used to remove the values of one table from the other. As we have used both same tables, the values used to update another table will be deleted.

>> Delete from system where exist (select name from sample1 where system.id = sample1.id );

When we fetch the table after deletion, we can see that a single row is left using the ‘select’ statement.

Conclusion

postgresql subquery” is an important feature of this platform to keep the queries easily understandable. In this article, we have discussed the use of subquery in each perspective of select, insert, update and delete commands. I am hopeful that this effort will aid you while using commands in postgresql.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.