MySQL Numeric Functions

The numeric functions in MySQL accept numeric values, perform a mathematic operation on the
values and return resulting sheet. Some useful numeric functions are:

[Link]
. Function Description Example

Returns the remainder of one expression SELECT MOD(11, 4)

1. MOD()
by diving y another expression. “Modulus” ;

Returns the value of one expression raised SELECT POWER(3, 2)

2. POWER()/POW()
to the power of another expression “Raised” ;

Returns numeric expression rounded to an

SELECT ROUND(15.193,
3. ROUND() integer. Can be used to round an
1) “Round” ;
expression to a number of decimal points.

This function returns sign of a given SELECT SIGN(-15)

4. SIGN()
number. “Sign” ;

Returns the non-negative square root of SELECT SQRT(26)

5. SQRT()
numeric expression. “Square root” ;

Returns numeric exp1 truncate to exp2 DRLRCT

6. TRUNCATE() decimal places. If exp2 is 0, then the result TRUNCATE(15.79, 1)
will have no decimal point “Truncate” ;

What Are Nested Queries in SQL?

A nested query (also called a subquery) is a query embedded within another SQL query. The
result of the inner query is used by the outer query to perform further operations. Nested queries
are commonly used for filtering data, performing calculations, or joining datasets indirectly.
Key Characteristics:
The inner query runs before the outer query.
The result of the inner query can be used by the outer query for comparison or as input data.
Nested queries are particularly useful for breaking down complex problems into smaller, manageable
parts, making it easier to retrieve specific results from large datasets.
To better understand nested queries, we will use the following sample tables: STUDENT,
COURSE, and STUDENT_COURSE. These tables simulate a real-world scenario of students, courses,
and their enrollment details, which will be used in the examples below.
1. STUDENT Table
The STUDENT table stores information about students, including their unique ID, name, address,
phone number, and age.

STUDENT Table
2. COURSE Table
The STUDENT_COURSE table maps students to the courses they have enrolled in. It uses the student
and course IDs as foreign keys.

COURSE Table
3. STUDENT_COURSE Table
This table maps students to the courses they have enrolled in, with columns for student ID (S_ID) and
course ID (C_ID):

Student_Course Table
Types of Nested Queries in SQL
1. Independent Nested Queries
In independent nested queries, the execution of the inner query is not dependent on the outer
query. The result of the inner query is used directly by the outer query. Operators like IN, NOT
IN, ANY, and ALL are commonly used with this type of nested query.
Example 1: Using IN
Find the S_IDs of students who are enrolled in the courses ‘DSA’ or ‘DBMS’.
Step 1: Find the C_IDs of the courses:
This query retrieves the IDs of the courses named ‘DSA’ or ‘DBMS’ from the COURSE table.
SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS');
Output

C_ID

C1

C3

Step 2: Use the result of Step 1 to find the corresponding S_IDs:

The inner query finds the course IDs, and the outer query retrieves the student IDs associated with
those courses from the STUDENT_COURSE table
SELECT S_ID FROM STUDENT_COURSE WHERE C_ID IN (
SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
);
Output

S_ID

S1

S2

S4

2. Correlated Nested Queries

In correlated nested queries, the inner query depends on the outer query for its execution. For
each row processed by the outer query, the inner query is executed. The EXISTS keyword is often
used with correlated queries.
Example 2: Using EXISTS
Find the names of students who are enrolled in the course with C_ID = ‘C1’:
SELECT S_NAME FROM STUDENT S
WHERE EXISTS (
SELECT 1 FROM STUDENT_COURSE SC
WHERE S.S_ID = SC.S_ID AND SC.C_ID = 'C1'
);
Output

S_NAME

RAM

RAMESH

Explanation:
For each student in the STUDENT table, the inner query checks if an entry exists in
the STUDENT_COURSE table with the same S_ID and the specified C_ID. If such a record exists, the
student’s name is included in the output.
Common SQL Operators for Nested Queries
1. IN Operator
The IN operator is used to filter rows based on a set of values returned by a subquery. It is
commonly used to match a column value against a list or result set. This operator simplifies queries
by avoiding the need for multiple OR conditions.
Example: Retrieve student names who enrolled in ‘DSA’ or ‘DBMS’:
This query filters the students enrolled in the specified courses by chaining multiple nested queries.
SELECT S_NAME FROM STUDENT
WHERE S_ID IN (
SELECT S_ID FROM STUDENT_COURSE
WHERE C_ID IN (
SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
)
);
2. NOT IN Operator
The NOT IN operator excludes rows based on a set of values from a subquery. It is particularly
useful for filtering out unwanted results. This operator helps identify records that do not match the
conditions defined in the subquery.
Example: Retrieve student IDs not enrolled in ‘DSA’ or ‘DBMS’:
This query excludes students who are enrolled in the courses ‘DSA’ or ‘DBMS’.
SELECT S_ID FROM STUDENT
WHERE S_ID NOT IN (
SELECT S_ID FROM STUDENT_COURSE
WHERE C_ID IN (
 SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
)
);
Output

S_ID

S3

EXISTS
The EXISTS operator checks for the existence of rows in a subquery. It returns true if the
subquery produces any rows, making it efficient for conditional checks. This operator is often used to
test for relationships between tables.
Example: Find student names enrolled in ‘DSA’
The inner query checks for matching records in the STUDENT_COURSE table, and the outer query
returns the corresponding student names.
SELECT S_NAME FROM STUDENT S
WHERE EXISTS (
SELECT 1 FROM STUDENT_COURSE SC
WHERE S.S_ID = SC.S_ID AND SC.C_ID = 'C1'
);
ANY and ALL
The ANY operator compares a value with any value returned by the subquery, while ALL ensures
comparison with all values.
Example: Compare student age with all ages in the table:
SELECT S_NAME FROM STUDENT
WHERE S_AGE > ALL (
SELECT S_AGE FROM STUDENT WHERE S_ADDRESS = 'DELHI'
);
Advantages of Nested Queries
Simplifies complex queries: Nested queries allow us to divide complicated SQL tasks into smaller,
more manageable parts. This modular approach makes queries easier to write, debug, and maintain.
Enhances flexibility: By enabling the use of results from one query within another, nested queries
allow dynamic filtering and indirect joins, which can simplify query logic.
Supports advanced analysis: Nested queries empower developers to perform operations like
conditional aggregation, subsetting, and customized calculations, making them ideal for
sophisticated data analysis tasks.
Improves readability: When properly written, nested queries can make complex operations more
intuitive by encapsulating logic within inner queries.
MySQL Subqueries
Introduction to Subqueries
A subquery is an SQL query nested inside a larger query. It can be nested inside a SELECT, INSERT,
UPDATE, DELETE, SET, or DO statement or inside another subquery. Subqueries are often used to
perform operations that are more complex and require multiple steps.
Where Subqueries Can Occur
SELECT clause: Used to return data based on specific criteria.

FROM clause: Used to define a temporary table to be used by the main query.

WHERE clause: Used to filter records based on certain conditions.

 In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement
or inside another subquery.

 A subquery is usually added within the WHERE Clause of another SQL SELECT statement.

 You can use the comparison operators, such as >, <, or =. The comparison operator can also be a
multiple-row operator, such as IN, ANY, SOME, or ALL.

 A subquery can be treated as an inner query, which is a SQL query placed as a part of another
query called as outer query.

 The inner query executes first before its parent query so that the results of the inner query can be
passed to the outer query.

Subqueries can return various types of results:

Scalar (a single value)

Row (a single row of data)

Column (a single column of data)

Table (one or more rows and columns)

Subquery Syntax:
The subquery (inner query) executes once before the main query (outer query) executes.
The main query (outer query) use the subquery result.
Subquery syntax as specified by the SQL standard and supported in MySQL
DELETE FROM t1
WHERE s11 > ANY
(SELECT COUNT(*) /* no hint */ FROM t2
WHERE NOT EXISTS
(SELECT * FROM t3
WHERE ROW(5*t2.s1,77)=
(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
(SELECT * FROM t5) AS t5)));
A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more
rows of one or more columns). These are called scalar, column, row, and table subqueries.
MySQL Subquery Example:
Using a subquery, list the name of the employees, paid more than 'Alexander' from emp_details .
mysql> SELECT first_name,last_name, salary FROM emp_details
WHERE salary >(SELECT salary FROM emp_details
WHERE first_name='Alexander');
+------------+-----------+----------+
| first_name | last_name | salary |
+------------+-----------+----------+
| Steven | King | 24000.00 |
| Neena | Kochhar | 17000.00 |
| Lex | De Haan | 17000.00 |
| RABI | CHANDRA | 15000.00 |
| Ana | King | 17000.00 |
+------------+-----------+----------+
5 rows in set (0.00 sec)
Subqueries: Guidelines
There are some guidelines to consider when using subqueries :
- A subquery must be enclosed in parentheses.
- Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-
row subqueries.
- If a subquery (inner query) returns a null value to the outer query, the outer query will not return
any rows when using certain comparison operators in a WHERE clause.
Types of Subqueries
The Subquery as Scalar Operand
Comparisons using Subqueries
Subqueries with ALL, ANY, IN, or SOME
Row Subqueries
Subqueries with EXISTS or NOT EXISTS
Correlated Subqueries
Subqueries in the FROM Clause
MySQL Subquery as Scalar Operand
A scalar subquery is a subquery that returns exactly one column value from one row. A scalar
subquery is a simple operand, and you can use it almost anywhere a single column value or literal is
legal. If the subquery returns 0 rows then the value of scalar subquery expression in NULL and if the
subquery returns more than one row then MySQL returns an error.
There is some situation where a scalar subquery cannot be used. If a statement permits only a literal
value, you cannot use a subquery. For example, LIMIT requires literal integer arguments, and LOAD
DATA INFILE requires a literal string file name. You cannot use subqueries to supply these values.
Example: MySQL Subquery as Scalar Operand
mysql> SELECT employee_id, last_name,
(CASE WHEN department_id=(
SELECT department_id from departments WHERE location_id=2500)
THEN 'Canada' ELSE 'USA' END)
location FROM employees;
+-------------+-------------+----------+
| employee_id | last_name | location |
+-------------+-------------+----------+
| 100 | King | USA |
| 101 | Kochhar | USA |
| 102 | De Haan | USA |
| 103 | Hunold | USA |
| 104 | Ernst | USA |
| 105 | Austin | USA |
| - - - - - - - - - - - - - - - - - - -|
| - - - - - - - - - - - - - - - - - - -|
107 rows in set (0.00 sec)
MySQL Subqueries: Using Comparisons
A subquery can be used before or after any of the comparison operators. The subquery can return at
most one value. The value can be the result of an arithmetic expression or a column function. SQL
then compares the value that results from the subquery with the value on the other side of the
comparison operator. You can use the following comparison operators:

Operator Description

= Equal to

> Greater than

>= Greater than or equal to

< Less than

<= Less than or equal to

!= Not equal to
 <> Not equal to

<=> NULL-safe equal to operator

For example, suppose you want to find the employee id, first_name, last_name, and salaries for
employees whose average salary is higher than the average salary throughout the company.

mysql> SELECT employee_id,first_name,last_name,salary

FROM employees WHERE salary >
(SELECT AVG(SALARY) FROM employees);
+-------------+-------------+------------+----------+
| employee_id | first_name | last_name | salary |
+-------------+-------------+------------+----------+
| 100 | Steven | King | 24000.00 |
| 101 | Neena | Kochhar | 17000.00 |
| 102 | Lex | De Haan | 17000.00 |
| 103 | Alexander | Hunold | 9000.00 |
| 108 | Nancy | Greenberg | 12000.00 |
| 109 | Daniel | Faviet | 9000.00 |
| 120 | Matthew | Weiss | 8000.00 |
| 121 | Adam | Fripp | 8200.00 |
| 122 | Payam | Kaufling | 7900.00 |
|- - - - - - - - - - - - - - - - - - - - - - - - - -|
|- - - - - - - - - - - - - - - - - - - - - - - - - -|
+-------------+-------------+------------+----------+
51 rows in set (0.00 sec)
MySQL Subqueries with ALL, ANY, IN, or SOME
You can use a subquery after a comparison operator, followed by the keyword ALL, ANY, or SOME.
The ALL operator compares value to every value returned by the subquery. Therefore ALL operator
(which must follow a comparison operator) returns TRUE if the comparison is TRUE for ALL of the
values in the column that the subquery returns.
Syntax:
operand comparison_operator ALL (subquery)
NOT IN is an alias for <> ALL. Thus, these two statements are the same:
Code:
SELECT c1 FROM t1 WHERE c1 <> ALL (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2);

Copy
Example: MySQL Subquery, ALL operator
The following query selects the department with the highest average salary. The subquery finds the
average salary for each department, and then the main query selects the department with the
highest average salary.
mysql> SELECT department_id, AVG(SALARY)
FROM EMPLOYEES GROUP BY department_id
HAVING AVG(SALARY)>=ALL
(SELECT AVG(SALARY) FROM EMPLOYEES GROUP BY department_id);
+---------------+--------------+
| department_id | AVG(SALARY) |
+---------------+--------------+
| 90 | 19333.333333 |
+---------------+--------------+
1 row in set (0.00 sec)
Note: Here we have used ALL keyword for this subquery as the department selected by the query
must have an average salary greater than or equal to all the average salaries of the other
departments.
The ANY operator compares the value to each value returned by the subquery. Therefore ANY
keyword (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ANY
of the values in the column that the subquery returns.
Syntax:
operand comparison_operator ANY (subquery)
Example: MySQL Subquery, ANY operator
The following query selects any employee who works in the location 1800. The subquery finds the
department id in the 1800 location, and then the main query selects the employees who work in any
of these departments.
employees table:

departments table:
mysql> SELECT first_name, last_name,department_id
FROM employees WHERE department_id= ANY
(SELECT DEPARTMENT_ID FROM departments WHERE location_id=1800);
+------------+-----------+---------------+
| first_name | last_name | department_id |
+------------+-----------+---------------+
| Michael | Hartstein | 20 |
| Pat | Fay | 20 |
+------------+-----------+---------------+
2 rows in set (0.00 sec)
Note: We have used ANY keyword in this query because it is likely that the subquery will find more
than one departments in 1800 location. If you use the ALL keyword instead of the ANY keyword, no
data is selected because no employee works in all departments of 1800 location
When used with a subquery, the word IN (equal to any member of the list) is an alias for = ANY. Thus,
the following two statements are the same:
Code:
SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2);

Copy
The word SOME is an alias for ANY. Thus, these two statements are the same:
Code:
SELECT c1 FROM t1 WHERE c1 <> ANY (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 <> SOME (SELECT c1 FROM t2);

Copy
MySQL Row Subqueries
A row subquery is a subquery that returns a single row and more than one column value. You can use
= , >, <, >=, <=, <>, !=, <=> comparison operators. See the following examples:
Code:
SELECT * FROM table1 WHERE (col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);
SELECT * FROM table1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);

Copy
For both queries,
if the table table2 contains a single row with id = 10, the subquery returns a single row. If this row
has col3 and col4 values equal to the col1 and col2 values of any rows in table1, the WHERE
expression is TRUE and each query returns those table1 rows.
If the table2 row col3 and col4 values are not equal the col1 and col2 values of any table1 row, the
expression is FALSE and the query returns an empty result set. The expression is unknown (that is,
NULL) if the subquery produces no rows.
An error occurs if the subquery produces multiple rows because a row subquery can return at most
one row.
Example: MySQL Row Subqueries
In the following examples, queries shows differentr result according to above conditions :
departments table:

employees table:
mysql> SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM
departments WHERE location_id = 1800);
+------------+
| first_name |
+------------+
| Pat |
+------------+
1 row in set (0.00 sec)
Code:
mysql>SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM
departments WHERE location_id = 2800);
Empty set (0.00 sec)

Copy
Code:
mysql>SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM
departments WHERE location_id = 1700);
ERROR 1242 (21000): Subquery returns more than 1 row

Copy
MySQL Subqueries with EXISTS or NOT EXISTS
The EXISTS operator tests for the existence of rows in the results set of the subquery. If a subquery
row value is found, EXISTS subquery is TRUE and in this case NOT EXISTS subquery is FALSE.
Syntax:
SELECT column1 FROM table1 WHERE EXISTS (SELECT * FROM table2);
In the above statement, if table2 contains any rows, even rows with NULL values, the EXISTS
condition is TRUE. Generally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT
'X', SELECT 5, or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery,
so it makes no difference.
Example: MySQL Subqueries with EXISTS
From the following tables (employees) find employees (employee_id, first_name, last_name, job_id,
department_id) who have at least one person reporting to them.
employees table:
SELECT employee_id, first_name, last_name, job_id, department_id
FROM employees E
WHERE EXISTS (SELECT * FROM employees WHERE manager_id = E.employee_id);
+-------------+------------+-----------+---------+---------------+
| employee_id | first_name | last_name | job_id | department_id |
+-------------+------------+-----------+---------+---------------+
| 100 | Steven | King | AD_PRES | 90 |
| 101 | Neena | Kochhar | AD_VP | 90 |
| 102 | Lex | De Haan | AD_VP | 90 |
| 103 | Alexander | Hunold | IT_PROG | 60 |
| 108 | Nancy | Greenberg | FI_MGR | 100 |
| 114 | Den | Raphaely | PU_MAN | 30 |
| 120 | Matthew | Weiss | ST_MAN | 50 |
| 121 | Adam | Fripp | ST_MAN | 50 |
| ---------- | ---------- | --------- | ------- | ------------- |
+-------------+------------+-----------+---------+---------------+
18 rows in set (0.02 sec)
Example: MySQL Subqueries with NOT EXISTS
NOT EXISTS subquery almost always contains correlations. Here is an example :
From the following table (departments and employees) find all departments (department_id,
department_name) that do not have any employees.
departments table:

employees table:
mysql> SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT * FROM employees WHERE department_id = d.department_id);
+---------------+----------------------+
| department_id | department_name |
+---------------+----------------------+
| 120 | Treasury |
| 130 | Corporate Tax |
| 140 | Control And Credit |
| 150 | Shareholder Services |
| 160 | Benefits |
| 170 | Manufacturing |
| 180 | Construction |
| 190 | Contracting |
| 200 | Operations |
| ------------ | -------------------- |
+---------------+----------------------+
16 rows in set (0.00 sec)
MySQL Correlated Subqueries
A correlated subquery is a subquery that contains a reference to a table (in the parent query) that
also appears in the outer query. MySQL evaluates from inside to outside.
Correlated subquery syntax:

Example - 1: MySQL Correlated Subqueries

Following query find all employees who earn more than the average salary in their department.
employees table:
mysql> SELECT last_name, salary, department_id
FROM employees outerr
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id =
outerr.department_id);
+-----------+----------+---------------+
| last_name | salary | department_id |
+-----------+----------+---------------+
| King | 24000.00 | 90 |
| Hunold | 9000.00 | 60 |
| Ernst | 6000.00 | 60 |
| Greenberg | 12000.00 | 100 |
| Faviet | 9000.00 | 100 |
| Raphaely | 11000.00 | 30 |
| Weiss | 8000.00 | 50 |
| Fripp | 8200.00 | 50 |
| -------- | -------- | ------------ |
+-----------+----------+---------------+
38 rows in set (0.02 sec)
Example - 2: MySQL Correlated Subqueries
From the employees and job_history tables display details of those employees who have changed
jobs at least once.
employees table:
job_history table:
mysql> SELECT first_name, last_name, employee_id, job_id
FROM employees E
WHERE 1 <= (SELECT COUNT(*) FROM Job_history WHERE employee_id = E.employee_id);
+------------+-----------+-------------+---------+
| first_name | last_name | employee_id | job_id |
+------------+-----------+-------------+---------+
| Neena | Kochhar | 101 | AD_VP |
| Lex | De Haan | 102 | AD_VP |
| Den | Raphaely | 114 | PU_MAN |
| Payam | Kaufling | 122 | ST_MAN |
| Jonathon | Taylor | 176 | SA_REP |
| Jennifer | Whalen | 200 | AD_ASST |
| Michael | Hartstein | 201 | MK_MAN |
+------------+-----------+-------------+---------+
7 rows in set (0.00 sec)
MySQL Subqueries in the FROM Clause
Subqueries work in a SELECT statement's FROM clause. The syntax is :
SELECT ... FROM (subquery) [AS] name ...
Every table in a FROM clause must have a name, therefore the [AS] name clause is mandatory. Any
columns in the subquery select list must have unique names.
Example: MySQL Subqueries in the FROM Clause
We have the following table tb1.
mysql> CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT); Query OK, 0 rows affected (0.73 sec)
Let insert some values into tb1.
mysql> INSERT INTO tb1 VALUES (1, '1', 1.0);
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO tb1 VALUES (2, '2', 2.0);

Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO tb1 VALUES (3, '3', 3.0);

Query OK, 1 row affected (0.03 sec)
mysql> select * from tb1;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1|1 | 1|
| 2|2 | 2|
| 3|3 | 3|
+------+------+------+
3 rows in set (0.00 sec)

Here is how to use a subquery in the FROM clause, using the example table (tb1) :
mysql> SELECT sc1, sc2, sc3
FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb
WHERE sc1 > 1;
+------+------+------+
| sc1 | sc2 | sc3 |
+------+------+------+
| 2|2 | 6|
| 3|3 | 9|
+------+------+------+
2 rows in set (0.02 sec)