MySQL SUBQUERY - Nested Queries

A SUBQUERY or nested query is a query within another query. It is used to perform more complex operations where the result of one query is used as a condition in another query. The basic syntax is:

SELECT column
FROM table1
WHERE column operator (SELECT column FROM table2 WHERE condition);

When using SUBQUERY, it's important to know:

Subquery must be enclosed in parentheses
Subquery is executed before the main query
You can use different operators (=, >, <, IN, EXISTS, etc.)
Subquery can only return one column (except in special cases)
Subqueries can be nested at multiple levels

EXAMPLE

CREATE TABLE company (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO company (id, name, department, salary) VALUES (1, 'John', 'Development', 2500.99);
INSERT INTO company (id, name, department, salary) VALUES (2, 'Mary', 'Marketing', 2300.89);
INSERT INTO company (id, name, department, salary) VALUES (3, 'Peter', 'Development', 2800.99);
INSERT INTO company (id, name, department, salary) VALUES (4, 'Anna', 'Finance', 2600.12);
INSERT INTO company (id, name, department, salary) VALUES (5, 'Tina', 'Marketing', 2400.54);

SELECT name,salary FROM company;

SELECT name, salary
FROM company
WHERE salary > (SELECT AVG(salary) FROM company);

RESULT

First, we create the "company" table with columns for ID, name, department, and salary.

CREATE TABLE company (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

We insert data for five employees with different salaries and departments.

INSERT INTO company (id, name, department, salary) VALUES (1, 'John', 'Development', 2500.99);
INSERT INTO company (id, name, department, salary) VALUES (2, 'Mary', 'Marketing', 2300.89);
INSERT INTO company (id, name, department, salary) VALUES (3, 'Peter', 'Development', 2800.99);
INSERT INTO company (id, name, department, salary) VALUES (4, 'Anna', 'Finance', 2600.12);
INSERT INTO company (id, name, department, salary) VALUES (5, 'Tina', 'Marketing', 2400.54);

Using the SELECT statement, we display the names and salaries of all employees.
```
SELECT name,salary FROM company;            
```
We use a subquery to find employees who have a salary higher than the average. The subquery (SELECT AVG(salary) FROM company) first calculates the average salary of all employees, then the main query shows only those employees whose salary exceeds this average.
```
SELECT name, salary
FROM company
WHERE salary > (SELECT AVG(salary) FROM company);            
```
The result shows two employees: Peter from Development department with salary 2800.99 EUR and Anna from Finance department with salary 2600.12 EUR, as they are the only ones who have a salary higher than the company average.