Single Row Sub Query in SQL

In SQL, Subqueries are two types.

  • Single-Row Subquery

  • Multi-Row Subquery

Let’s discuss these in details.

 

Single Row Subquery

A single row subquery returns zero or one row to the outer SQL statement. You can place a subquery in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement.

 

In Single Row subquery we can use only single row comparison operators like-

Equal to (=), Greater than (>), Less than (<), Greater than equal to (>=). Less than equal to (<=) and Not equal to (<>).

 

Single Row Subquery in WHERE clause

You can place a subquery in the WHERE clause of another query. Let’s take an example of a query that contains a subquery placed in it’s WHERE clause.

 

Example – Find the Employees name and salary who has the salary greater than Matt.

SQL> Select First_name, Salary from employees

          WHERE Salary > (Select salary from employees where First_name = Matt);

In the above example, subquery will send Matt’s salary to outer query and outer query use the subquery result to find the salaries of the employees who are getting more than Matt.

 

Subqueries in HAVING clause

HAVING clause is used to filter groups of rows. You may place a subquery in HAVING clause in an outer query. This allows you to filter groups of rows based on the result returned by your subquery.

 

The following example uses a subquery in the HAVING clause of the outer query.

Example – Find Department ID and an average salary of Departments, who has the greater average salary from Department no 50.

SQL> SELECT Department_ID, AVG(Salary)

          FROM Employees   

          GROUP BY department_id  

          HAVING AVG(Salary) > 

                         (SELECT AVG(Salary)   

                          FROM Employees  

                          WHERE department_id = 50 )); 

 

Let’s under above example. The subquery will return the average salary of the Department_id of 50. The outer query will use the result of the subquery and return the average salary of all departments, whose average salary is greater than department 50.

0 Comment

Leave a Reply