SQL subquery is a very interesting and important topic. Sometimes we stuck at the very common problem at there we want to get the data from the one query and on the basis of their result, we want to execute another query.
Let’s Take An Example
In this example we will take two tables:–
CREATE TABLE employee ( id INT NOT NULL PRIMARY KEY, first_name VARCHAR(256), last_name VARCHAR(256), designation VARCHAR(256) );
CREATE TABLE employee_salary ( id INT NOT NULL PRIMARY key, salary DECIMAL, employee_id INT, FOREIGN KEY(employee_id) REFERENCES employee(id) );
Problem: Now I want to fetch those employee details whose salary is greater than our software engineer Reena. But we don’t have any idea about Reena’s Salary.
Before going to perform any action lets read out the script.
We can use the comparison operators, like >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.
Subquery also is known as an inner query.
SELECT * FROM employee INNER JOIN employee_salary ON employee.id=employee_salary.employee_id WHERE employee_salary.salary > (SELECT salary FROM employee_salary WHERE employee_salary.employee_id=2);
SELECT * FROM employee,employee_salary WHERE employee.id=employee_salary.employee_id AND employee_salary.salary > (SELECT salary FROM employee_salary WHERE employee_salary.employee_id=2);