UrbanPro
true

Learn Database Training from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

SQL Subqueries - Used in various ways

Gavi Y.
27/12/2016 0 0

If you master the concepts of subqueries, you would gain a lot of flexibility in writing complex SQL queries. Let's not get into Correlated subqueries, let us see the different versions of nested subquery with some simple examples.
I am attaching the EMP table data as a document in my profile, pls download it and have it inserted in your DB.

What is a Subquery?
A query within a query is Subquery.

For Eg:
Select * from EMP;

Empid Ename Sal Deptid
1 Mohit 50000 10
2 Vikas 40000 20
3 Naveen 30000 30
4 Pravin 20000 40
5 Rohit 10000 50
6 Gavi 60000 10
7 Ramesh 42000 20
8 Manav 33000 30
9 Manoj 27000 40
10 Nilesh 11000 50
11 Vinay 52000 10

To find out all the employees who work in the department of 'Nilesh'
Select * from emp where deptid in (Select deptid from emp where ename='Nilesh');

Further, maximum of 255 nested subquery levels are allowed in Oracle in WHERE clause.

Adding to that you can use subquery in HAVING clause, FROM Clause and SELECT clause also, just like in WHERE Clause.

Eg. for HAVING CLAUSE subquery:
To find out the departments whose department-wise average salary is more than the avg salary of the whole organization.
Select Deptid,count(*),avg(sal) from EMP group by Deptid having avg(sal)>(Select avg(sal) from emp);

Eg. for FROM CLAUSE subquery:
To find out what are the employees salary and minimum salary in their department.
Select Empid,Ename,e.Deptid, Sal, MinSal.min_sal from emp e, (Select deptid,min(sal) min_sal from emp group by deptid) MinSal
where e.deptid = minsal.deptid Order by e.deptid,e.sal

Another common example for FROM CLAUSE Subquery is to find out the 5 most well paid employees.
Select * FROM (Select * from EMP ORDER BY SAL DESC) WHERE Rownum<6

Eg. for SELECT CLAUSE subquery:
To find out the difference between an employee salary and his department avg salary for all the employees.

Select Empid,Ename,Deptid, Sal, Sal-(Select avg(sal) from emp where deptid=e.deptid) DIFF from emp e order by deptid;

The example queries used here can be written in a better and efficient way, but my purpose here is to make it easier to understand for the reader.
Also, we have used only one table. In real life scenarios, you would face lots of tables and more complex situations.
Another importan thing is that joins always work faster than subqueries. But it again depends on optimizer, if it is smart enough and generates the same execution plan for both the queries, you will get the same response time.

0 Dislike
Follow 1

Please Enter a comment

Submit

Other Lessons for You

Microsoft Excel
Software developed and manufactured by Microsoft Corporation that allows users to organize, format, and calculate data with formulas using a spreadsheet system broken up by rows and columns. Microsoft...

Big Data & Hadoop - Introductory Session - Data Science for Everyone
Data Science for Everyone An introductory video lesson on Big Data, the need, necessity, evolution and contributing factors. This is presented by Skill Sigma as part of the "Data Science for Everyone" series.

PowerPivot For Excel
PowerPivot is an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous...

10 Study Tips to Achieve your Goals
This is particularly true of students that are looking to make the most of their study time and get better grades at Organization. 1. Set Study Goals: There is lots of credible research suggesting that...

SQL is not a Rocket Science
I am a Professional and at the inital days I completely hate Databases but I forgot to know that "Everything starts and ends with us".It means the INTEREST starts with us. Everybody knows this but none...
X

Looking for Database Training Classes?

The best tutors for Database Training Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn Database Training with the Best Tutors

The best Tutors for Database Training Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more