SQL Subqueries

 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

output

Tree

Related post

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

Leave a Reply to online Cancel reply

Your email address will not be published. Required fields are marked *