SQL Subqueries

MySQL
Tree
April 21, 2020
9
2211
5 minutes read

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

Post Views: 2,376

9 Comments

Shefali Nema says:
October 4, 2020 at 5:55 pm

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
;
- Reply
  Deepak Saroj says:
  February 15, 2021 at 5:02 am
  
  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).

720p izle says:
December 10, 2020 at 12:59 am

Really appreciate you sharing this article post. Thanks Again. Awesome. Martynne Carroll Chud

dublaj says:
February 1, 2021 at 4:26 am

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

torrent says:
February 1, 2021 at 6:19 am

Some really wonderful info , Gladiola I noticed this. Gale Trumaine Hake

filmkovasi says:
February 1, 2021 at 9:09 am

As soon as I detected this site I went on reddit to share some of the love with them. Christye Ashlin Hyps

online says:
February 1, 2021 at 11:42 am

This can be an inspiration to many people. Very good job Mercedes Vidovik Godard

tek parca says:
February 1, 2021 at 11:20 pm

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