Using Subqueries in Queries (USQ)

In complex SQL commands, there are often queries within queries. This is called a subquery or often referred to as the inner SELECT command.

There are 3 types of subqueries, namely:

1. Scalar subquery returns one column and one row each so it is a single value. An example is when you want to find the highest salary of all existing employees.
2. Row subquery will return a single row with multiple columns. An example is when you want to compare the highest and lowest salaries of all employees.
3. The subquery table will return multiple columns and multiple rows.

Following are some common uses of subqueries:

1. Subquery with equality

The use of this subquery aims to search for data with conditions that are adjusted to the data in the table in the subquery. Generally used to search for data values ​​in other tables to adjust existing data. The script below shows an example of a SQL command to retrieve employee data in the Administration department.

SELECT * FROM employees
WHERE department_id = (
SELECT department_id FROM departments
WHERE department_name LIKE 'Administration'
);

2. Subquery with aggregate function

There are several rules regarding subqueries with aggregate functions, namely:

• ORDER BY may not be used in a subquery (even if it is used in the outermost SELECT command).
• The SELECT statement in a subquery must consist of a single column, unless EXISTS is used in the subquery in question.
• When a subquery is one of two operands involved in a comparison, it must be placed on the right side of the comparison. The script below shows an example of using a subquery with an aggregate.

SELECT first_name || ' ' || last_name, salary
FROM employees
WHERE (SELECT AVG (salary) FROM employees)
< salary

Hope this is useful & happy learning!