SQL Subqueries
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:–
1)employee
2)employee_salary
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.
Solution #1
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);
Solution #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);
Output Of Both Solution
9 Comments
The WITH clause, or subquery factoring clause can also be used. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. Putting down below an example for the same scenario:
WITH temp_salary as (select id,employee_id, salary FROM employee_salary WHERE employee_id=2)
SELECT * FROM employee INNER JOIN temp_salary
on employee.id=temp_salary.employee_id
;
At first, you might think that there’s almost no difference between subqueries and CTEs. We’ve used both a subquery and a CTE in the FROM clause and the syntax was only a little different.
This returns exactly the same result as the previous example with the subquery. However, instead of writing our query inside the FROM clause, we’ve put it in the WITH clause (which comes before the main query).
Really appreciate you sharing this article post. Thanks Again. Awesome. Martynne Carroll Chud
Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you could do with a few pics to drive the message home a little bit, but other than that, this is fantastic blog. A fantastic read. I will definitely be back. Annissa Keenan Koffler
Some really wonderful info , Gladiola I noticed this. Gale Trumaine Hake
As soon as I detected this site I went on reddit to share some of the love with them. Christye Ashlin Hyps
This can be an inspiration to many people. Very good job Mercedes Vidovik Godard
Awesome read. I just passed this onto a buddy who was doing some research on that. He actually bought me lunch since I found it for him! So let me rephrase: Thanks for lunch! Luci Skipp Joelle
Fantastic article post. Thanks Again. Really Great. Jacqueline David Kaden