Scalar Subqueries in sql
In SQL database management systems, a scalar subquery is a type of database table-based generated subquery that returns a single value from a user-generated table. This means it returns only one table row and one column value to the database user. These subquery values can be used in external database query expressions or comparisons. In database management, scalar subqueries are most commonly used to return a single part value of table data, which is then used to evaluate multiple condition values in an external query as needed.
Remember, scalar subqueries in database tables return a single value at a time, which is why they are most often used with comparison operators such as =, <, >, <=, >=, and <>, or in clauses or expressions such as SELECT, WHERE, HAVING, and SET.
Features of Scalar Subqueries in Database Tables.
- Single Value Return – A scalar subquery in a database table must return only one column and one row.
- Embedded within another query – Scalar subqueries in database tables are used within another query, typically in the SELECT, WHERE, HAVING, or SET clauses.
- Used with Comparison Operators – Scalar subqueries are frequently used with comparison operators such as =, >, <, etc., to filter table data in detail.
Basic Scalar Subquery Syntax.
SELECT column_name
FROM table_name
WHERE column_name = (
SELECT single_value_column
FROM another_table
WHERE condition
);
In this Basic Scalar Subquery.
- The subquery (inner query) returns a single value.
- The outer query uses that value with a comparison operator condition.
Example of a Scalar Subquery.
- Creating a Scalar Subquery with a WHERE Clause in an SQL Database.
- Here, let’s assume you have two separate database tables named Employees and Departments.
- This is the employee table, which has table column fields named employee_id, emp_name, and salary.
- This is the department table, which has table column fields named department_id and department_name.
Here, the database user wants to extract the name of every employee from the employee table who earns the highest salary in the current company. This can be performed by applying a scalar subquery in the WHERE clause.
SELECT emp_name
FROM employee
WHERE salary = (
SELECT MAX(salary)
FROM employee
);
Explanation of the Employee and Department table subquery.
- Here, the inner query uses the MAX() function to find the maximum employee salary in the employee table.
- Here, the outer query extracts the name of the employee where the employee’s salary is equal to the maximum salary.
Scalar Subquery in the SELECT Clause in a Database Table.
Database users can also apply a scalar subquery in the SELECT clause to return a calculated value for each table row of the outer query. For example, let’s say the database user wants to find the name of each employee and compare their salary to the employee who earns the highest salary in the company.
SELECT emp_name,
salary,
(SELECT MAX(salary) FROM employee) AS highest_salary
FROM employee;
Explanation of the Scalar Subquery in the Employee table query.
Here, the inner query returns a single value, which is the highest salary earned by an employee in the company. Here, the outer query extracts the employee’s name and salary information, and it also displays the information of the employee with the highest salary in each employee’s company in the employee table. Here, the database table subquery is executed once for the complete result output, and the subquery result output is repeated in each row.
Database Table Scalar Subquery with Comparison Operator.
Here, let’s assume the database user wants to extract the names of employees whose salaries are greater than the average salary of employees in the same department. Therefore, the database user can apply a scalar subquery to calculate the department-wise average salary and compare it with the salaries of individual employees.
SELECT emp_name
FROM employee e
WHERE salary > (
SELECT AVG(salary)
FROM employee
WHERE department_id = e.department_id
);
Explanation of Scalar Subquery with Comparison Operator.
- Here, the inner query in the scalar subquery calculates the average salary for each employee’s department.
- Similarly, the outer query extracts the employee information from the table for those employees whose salaries are greater than the average salary of their department.
- Here, the user-generated subquery is of a correlated nature because it directly refers to the department_id from the outer query’s e.department_id. The same subquery is executed again for each table row in the outer query, where each employee’s salary is compared with the average salary of their department.
Scalar Subquery with Aggregated Data Preview.
Scalar subqueries in SQL databases are especially useful when applying aggregate numeric functions such as MAX(), MIN(), and AVG(). Here’s an example where a database user wants to find the employee information for employees whose salary is greater than the average salary in the company.
SELECT emp_name
FROM employee
WHERE salary > (
SELECT AVG(salary)
FROM employee
);
Explanation of the subquery and aggregate function.
- The inner query calculates the total average salary from the employee table.
- The outer query then extracts the names of the employees whose salary is greater than the calculated average salary.
Scalar Subqueries with Multiple Conditions in Database Tables.
Database users can also use scalar subqueries with multiple conditions in the outer query. For example, a database user can compare multiple table values, such as finding out if an employee’s salary is greater than the average salary in the company, and whether the employee’s department has more than a fixed number of employees.
SELECT emp_name
FROM employee
WHERE salary > (
SELECT AVG(salary)
FROM employee
) AND department_id IN (
SELECT department_id
FROM employee
GROUP BY department_id
HAVING COUNT(*) > 13
);
Explanation of Scalar Subqueries with Multiple Conditions.
- The first scalar subquery calculates the average salary of all employees.
- The second subquery finds the employee departments that have more than 13 employees.
- The outer query then extracts the names of the employees whose salary is greater than the average salary and who belong to a department with more than 13 employees.
Conclusion on Scalar Subqueries in SQL Database Management Systems.
- Scalar subqueries in SQL databases are an advanced and powerful feature that allows database users to embed an inner query within another outer query to return a single table value. Scalar subqueries are most commonly used in the WHERE, SELECT, and HAVING clauses of database queries for activities such as comparing the value of a column in a database table to a single value calculated by the subquery.
- This includes calculating values by applying aggregate functions such as AVG(), MAX(), and SUM(), and filtering or extracting table data based on the results of another query.
