PostgreSQL Subquery

In this section, we are going to understand the working of PostgreSQL Subquery, which allows us to create a difficult query. And we also see examples of subqueries with different clauses such as SELECT, FROM, and WHERE, different Conditions such as IN, EXISTS, and different Queries such as SELECT, INSERT, UPDATE, and DELETE.

Introduction of PostgreSQL Subquery

A subquery is a command used within another query. In contrast, the INNER SELECT or the INNER statement is called a SUBQUERY, and OUTER SELECT or the OUTER statement is called the MAIN command. The PostgreSQL subquery is enclosed in parentheses.

The PostgreSQL subquery can be used with different clauses such as SELECT, FROM, WHERE and HAVING clauses.

We can also combine the PostgreSQL subquery with the SELECT, INSERT, UPDATE, and DELETE commands as well as different operators such as <,>, =, <=,>=, BETWEEN, IN, and so on.

Note: In PostgreSQL, a Subquery is not allowed to use with the ORDER BY clause. But we can use the GROUP BY command to perform similar functionality in place of the ORDER BY command.

Examples of PostgreSQL Subquery With different Clauses

Let us see different examples to understand how the PostgreSQL subquery works with various clauses.

Example of PostgreSQL Subquery with SELECT clause

The PostgreSQL subquery can also be combined with the SELECT clause.

The subqueries are used when we want to fetch a calculation with the help of an aggregate function like Average, Count, Sum, Max, and Min function, but we do not want the aggregate function to use into the MAIN query.

Here, we will display how to use the Subquery with the SELECT clause.

We are taking the car table from the javatpoint database to select the particular data from the table.

SELECT c1.car_name, c1.car_model,
 	(SELECT MIN (car_id)
   	 FROM car c2
  	 WHERE c1.car_id = c2.car_id) Subquery1
FROM car c1; 

Output

After executing the above command, we will get the following output:

In the above example, we have created a Subquery in the SELECT clause like below:

And we have aliasing the subquery as the name Subquery1, which will be the name used to refer the above subquery or any of these fields.

NOTE: The aggregate functions like Average, Count, Sum, Max, and Min function are regularly used in the subquery. And the subquery must return a single value, that's why we are placing a subquery in the SELECT clause.

Example of PostgreSQL Subquery with FROM clause

The PostgreSQL Subquery can also be combined with the FROM clause.

For this, we are taking the Course and Course_categories table from the javatpoint database, which we created in the earlier section of the PostgreSQL tutorial.

SELECT course.course_name, Subquery2.course_category_id
FROM course,
 	(SELECT course_categories.course_category_id, course_categories.course_category, 
  	COUNT (course_category_id) AS total
  	FROM course_categories
  	GROUP BY course_categories.course_category_id, course_categories.course_category) Subquery2
WHERE Subquery2.course_category_id = course.course_id;

Output

After successful executing of the above command, we will get the following output:

In the above example, we have created a Subquery in the FROM clause like below:

And we have aliasing the subquery as a name Subquery2, which will be the name used to refer the above subquery or any of these fields.

Example of PostgreSQL Subquery with WHERE clause

The PostgreSQL subquery most commonly used with the WHERE clause. And these Subqueries are also known as the Nested Subqueries.

For this, we are taking the Client and Client_details table from the Javatpoint database, which we created in the earlier section of the PostgreSQL tutorial.

SELECT c.client_id, c.client_name, c.client_profession
FROM client c
WHERE c.client_id IN
  	(SELECT cd.client_id
    	FROM client_details cd
    	WHERE cd.client_id < 6
   	AND c.client_name LIKE 'M%');   

Output

On implementing the above command, we will get the following result:

In the above example, we have created a Subquery in the WHERE clause like below:

The above subquery statement will allow us to identify all client_id values from the client_details table with a client_id less than 6, and the client_name starts with 'M'. The subquery is used to filter the MAIN query's output with the IN-condition's help.

In the below command, we have used with an INNER Join condition as an alternative of Subquery follows:

SELECT c.client_id, c.client_name, c.client_profession
FROM client c
INNER JOIN Client_details cd
ON c.client_id= cd.client_id
WHERE cd.client_id < 6
AND c.client_name LIKE 'M%';

Output

After executing the above command, we will get a similar result as compared to the above subquery command with WHERE clause:

Note: The INNER JOIN condition will perform more efficiently as compared to the original subquery, and it is significant to see that not all subqueries can be re-written with the help of PostgreSQL joins.

Examples of PostgreSQL Subquery With different Conditions

Let us see different examples to understand how the PostgreSQL subquery works with different conditions.

Example of PostgreSQL Subquery with EXISTS Condition

The PostgreSQL Subquery can also be combined with the EXISTS clause.

The below syntax is used to display the working of a Subquery with EXISTS Condition:

The EXISTS condition only kept the number of rows returned from the subquery, and not the content of the rows. Hence, we can use the following syntax of EXISTS condition:

EXISTS 
(SELECT 1 
FROM table_name 
WHERE condition);

A subquery can be an input of the EXISTS condition. If the Subquery returns any row, then the EXISTS condition will return TRUE.
And if the subquery will return no row, then the output of the EXISTS condition will return FALSE.

Let's see one sample example for our better understanding of the Subquery with the EXISTS condition.

For this, we are taking the employee and department table from the javatpoint database, which we created in the earlier section of the PostgreSQL tutorial.

SELECT emp_fname, emp_lname
FROM employee
WHERE EXISTS (
		SELECT 1
		FROM department
		WHERE department.emp_id = employee.emp_id
	);

Output

After executing the above command, we will get the following output:

As we can see in the above screenshot that the commands work like PostgreSQL Inner Join on the emp_id column.

But it returns at least one row in the employee table, although there are some matching rows in the department table.

Example of PostgreSQL Subquery with IN Condition

The PostgreSQL subquery can also be combined with IN Condition.

Here, we are using the subquery with IN Condition and the WHERE clause.

In the following example, we take a similar table (employee and department) as the above example.

Here, we will try to fetch those employee's information which has the joining_date between 2010-08-22 and 2010-08-26:

SELECT department.emp_id
FROM employee
INNER JOIN department
ON department.emp_id = employee.emp_id
WHERE joining_date 
BETWEEN '2010-08-22' AND '2010-08-26';

Output

On executing the above command, we will get the below result:

The above command will return various rows. Therefore, we can use this command as a subquery in the WHERE clause of the following statement:

SELECT job_id, job_description
FROM jobs
WHERE job_id IN (
			SELECT department.emp_id
			FROM employee
			INNER JOIN department 
			ON department.emp_id = employee.emp_id
			WHERE joining_date 
			BETWEEN '2010-08-22' AND '2010-08-26'
			);

Output

After successful execution of the above command, we will get the following output:

Examples of PostgreSQL Subquery With different statements

Let us see different examples to understand how the PostgreSQL subquery works with several statements.

Example of PostgreSQL Subquery with SELECT Statement

We will display using the Subquery with the SELECT command.

For this, we are taking the Car table from the Javatpoint database to retrieve all the records from the table.

Let us assume that we need to identify those cars whose car_price is higher than the average car_pirce. So, for this, we will perform the below two steps:

Step1

Firstly, we will identify the average car_price with the SELECT command and (AVG) average function.

The below statement is used to fetch the average car_price from the car table:

SELECT
AVG (car_price)
FROM car;

Output

After successfully executing the above command, we will get the following output:

As we can see in the below screenshot that the average car_price is 103109.500.

Step2

After that, we will take the output of the first command in the second SELECT command to identify cars from the car table.

In the below command, we will get that car_id, car_name whose car_price is greater than the average car_price:

SELECT car_id, car_name, car_price
FROM car
WHERE car_price >103109.5;

Output

On implementing the above command, we will get the following result:

As we can see, the above code is not well designed because it requires two steps to implement a query. Hence, we need to permit the first command's output to the second command in a single command.

In such a case, we will use the concept of PostgreSQL subquery because a subquery is a command, which is nested into the other commands like INSERT, SELECT, UPDATE, and DELETE.

In this section of the PostgreSQL tutorial, we will understand all the statements one by one.

Syntax of PostgreSQL Subquery with SELECT Statement

The syntax of the PostgreSQL subquery with SELECT command is as follows:

SELECT column_name  
FROM table_name  
WHERE column_name expression operator(
SELECT column_name  
FROM able_name 
WHERE condition);  

To create a subquery, we put the second command in parenthesis along with the WHERE clause as an expression:

SELECT car_id, car_name, car_price
FROM car
WHERE car_price >(
     		SELECT
			AVG (car_price)
			FROM
			car );

Output

After executing the above command, we will get the following output:

As we can observe that, after executing both the command, we will get similar output, but the Subquery command is more efficient and readable.

Note: The PostgreSQL executes those commands which contain a subquery in the below sequence:

Firstly, it will implement the subquery.
Then, it will fetch the output and passes it to the OUTER SELECT or the OUTER QUERY.
At last, it will execute the OUTER SELECT.

Example of PostgreSQL Subquery with INSERT Statement

We will display how to use the Subquery with the INSERT statement. And in the INSERT command, the records return from the subquery is used to insert into another table.

In the PostgreSQL subquery, the selected data can be changed with any date functions and character.

Syntax of PostgreSQL Subquery with INSERT Statement

The syntax of the PostgreSQL subquery with INSERT command is as follows:

INSERT INTO table_name (column1, column2,.. columnN)   
SELECT column_names
FROM table_name  
WHERE VALUE OPERATOR

For this, we are taking the employee and department table from the Organization database and also using the AND Operator to insert the records from one table to another table.

In the following example, we are inserting the record into the employee table from the department table.

We take the phone and address columns records from the department table, the dept_id is less than 5, AND department_name is OPERATION.

INSERT INTO employee
(phone, address)
SELECT phone, address
FROM department
WHERE dept_id < 5
AND department_name = 'OPERATION';

Output

After executing the above command, we will get the below message window displaying that the value has been inserted successfully in the employee table.

To check whether the records have been inserted into the employee table or not, we will use the SELECT command as follows:

Output

After implementing the above command, we will get the below output:

As we can see in the above screenshot that the PostgreSQL Subquery inserted one record into the employee table.

Example of PostgreSQL Subquery with UPDATE Statement

We will display how to use the Subquery with the UPDATE statement. If we used a Subquery with the UPDATE command, then either one or more than one column in a table can be updated.

Syntax of PostgreSQL Subquery with UPDATE Statement

The syntax of the PostgreSQL Subquery with UPDATE command is as follows:

UPDATE table  
SET column_name = new_value  
WHERE VALUE OPERATOR (
   		SELECT column_name 
   		FROM table_name 
   		WHERE condition
);  

For this, we are taking the Summer_fruits and Winter_fruits tables from the Javatpoint database, which we created in the earlier section of the PostgreSQL tutorial.

In the following command, the PostgreSQL Subquery is used to update the Fruits_name column values in the summer_fruits table from the winter_fruits_name column in the winter_fruits table where the winter_fruits' wf_id column is equal to summer_fruits' sf_id column.

UPDATE summer_fruits
SET Fruits_name=(SELECT winter_fruits.winter_fruits_name
                     	FROM winter_fruits
                     	WHERE winter_fruits.wf_id = summer_fruits.sf_id);

Output

After executing the above command, we will get the below output, where we can see that the summer_fruits table has been updated successfully.

We will now use the Select command to check whether the particular records have been updated or not in the summer_fruits table:

Output

On executing the above command, we will get the below result:

As we can see in the above screenshot that the PostgreSQL Subquery updated Six records into the summer_fruits table.

Example of PostgreSQL Subquery with DELETE Statement

We will display how to use the Subquery with the DELETE statement, just like any other statement mentioned above.

Syntax of PostgreSQL Subquery with DELETE Statement

The syntax of the PostgreSQL Subquery with DELETE command is as follows:

DELETE 
FROM table_name  
WHERE value operator (
SELECT column_name  
  	FROM table_name 
   	WHERE condition
);   

In the below example, we take the Course and Course_categories table from the Javatpoint Database and also used the EXISTS operator for deleting the particular records from the table.

In the following command, the PostgreSQL Subquery is used to delete all the records from the Course table where the course_id from Course table, and course_category_id from the Course_categories table are equal.

DELETE FROM Course
WHERE EXISTS (
		SELECT course_name
       		FROM Course_categories
       		WHERE Course.course_id = Course_categories.course_category_id
);

Output

After executing the above command, we will get the below message window displaying that the records have been deleted successfully from the Course table.

We will now use the Select command to check whether the particular records have been deleted or not in the Course table:

SELECT * 
FROM Course;

Output

After successfully executing the above command, we will get the following output:

Overview

The use of PostgreSQL Subquery helps us to create the complex command. In the PostgreSQL Subquery section, we have learned the following topics:

We used the Subquery with SELECT clause to select the particular table records.
We used the Subquery with FROM clause to get a list of the tables.
We used the Subquery with WHERE Clause to filter the output and apply conditions.
We used the Subquery with EXISTS Condition to check whether the condition has been met once the subquery returns at least one row.
We used the Subquery with IN Condition test if an expression matches any value in a list of values.
We used the Subquery with the SELECT command to get the particular table records.
We used the Subquery with INSERT command to insert the records from one table to another.
We used the Subquery with the UPDATE statement to update the table's records.
We used the Subquery with the DELETE statement to remove the particular table's records.