Understanding Subqueries in SQL

This blog explains subqueries in SQL. We will explore what are nested queries and sub-queries and how they help retrieve data from complex databases.

In my previous articles, you have understood what is MySQL and its basic functions. In this article, we will discuss nested queries and subqueries in SQL to retrieve data from complex databases.

Nested & Subqueries in SQL

Nested queries have outer queries and inner sub queries. Multiple subqueries can be nested inside another query by using the SELECT, INSERT, UPDATE, and DELETE commands.

Nested queries are most commonly used when you have to retrieve specific data from multiple tables, along with additional criteria. 

Refer to the image below:

Note:

• Inner Query is executed first
• You can have any number of subqueries in the nested queries
• The performance of the query might be affected if nested way too much
• SELECT in subqueries can only retrieve a single column
• Use indentation while writing queries to make it easy for the users to understand
• You can use subqueries for calculations as well

Top Interview Questions on Nested Queries

In this article, I am going to consider the following tables to explain to you the most asked interview questions.

Patients Table

PatientsCheckup Table

Read Also: Update Query in SQL

Must Read: SQL Logical Operator

Also Read: How to use UNION in SQL

Q1. Find the Nth highest consultation fees from the PatientsCheckup table with and without using the TOP/LIMIT keywords.

The Nth highest consultation fees from the PatientsCheckup table with using the TOP keywords

SELECT TOP 1 ConsultationFees
FROM(
SELECT TOP N ConsultationFees
FROM PatientsCheckup
ORDER BY ConsultationFees DESC) AS FEES
ORDER BY ConsultationFees ASC;
 

The Nth highest consultation fees from the PatientsCheckup table using the LIMIT keywords.

SELECT ConsultationFees
FROM PatientsCheckup
ORDER BY ConsultationFees DESC LIMIT N-1,1;

Nth highest consultation fees from the PatientsCheckup table without using the TOP/LIMIT keywords.

SELECT ConsultationFees
FROM PatientsCheckup F1
WHERE N-1 = (
      SELECT COUNT( DISTINCT ( F2.ConsultationFees ) )
      FROM PatientsCheckup F2
      WHERE F2.ConsultationFees >  F1.ConsultationFees );

Q2. Write a SQL query to fetch the first and last record of the Patients table.

–FETCH FIRST RECORD
SELECT * FROM Patients WHERE PatientID = (SELECT MIN(PatientID) FROM Patients);
 
–FETCH LAST RECORD
SELECT * FROM Patients WHERE PatientID = (SELECT MAX(PatientID) FROM Patients);

Q3. Write a query to find those patients who have paid consultation fees between 200 to 300.

SELECT * FROM Patients WHERE PatientID IN 
(SELECT PatientID FROM PatientsCheckup WHERE Salary BETWEEN '200' AND '300');

Q4. Write a SQL query to retrieve the last 4 records from the Patients table.

SELECT * FROM Patients WHERE
PatientID <=4 UNION SELECT * FROM
 (SELECT * FROM Patients P ORDER BY P.PatientID DESC)
AS P1 WHERE P1.PatientID <=4;

Q5. Write a query to fetch PatientIDs:

a. Which are present in both tables

b. Which are present in one of the table. Let us say, patients present in Patients and not in the PatientsCheckup table.

–Present IN BOTH TABLES
SELECT PatientId FROM Patients 
WHERE PatientId IN 
(SELECT PatientId FROM PatientsCheckup);
 
– Present IN one OF the TABLE
SELECT PatientId FROM Patients 
WHERE PatientId NOT IN 
(SELECT PatientId FROM PatientsCheckup);

Q6. Write a query to fetch even and odd rows from a table by diving by 2.

Fetch even rows in SQL Server

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY PatientId) AS RowNumber
    FROM Patients
) P
WHERE P.RowNumber % 2 = 0;

Fetch even rows in MySQL

SELECT * FROM (
      SELECT *, @rowNumber := @rowNumber+ 1 rn
      FROM Patients
      JOIN (SELECT @rowNumber:= 0) r
     ) p 
WHERE rn % 2 = 0;

In case  you wish to find the odd rows, then the remainder when divided by 2 should be 1

Fetch odd rows in SQL Server

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY PatientId) AS RowNumber
    FROM Patients
) P
WHERE P.RowNumber % 2 = 1;

Fetch odd rows in MySQL

SELECT * FROM (
      SELECT *, @rowNumber := @rowNumber+ 1 rn
      FROM Patients
      JOIN (SELECT @rowNumber:= 0) r
     ) p 
WHERE rn % 2 = 1;

Q7. Write a SQL query to fetch 50% records from the PatientsCheckup table.

SELECT *
FROM PatientsCheckup WHERE
PatientID <= (SELECT COUNT(PatientD)/2 FROM PatientsCheckup);

Q8. Write a query to retrieve two minimum and maximum consultation fees from the PatientsCheckup Table.

– TWO MINIMUM ConsultationFees
SELECT DISTINCT ConsultationFees FROM PatientsCheckup P1
 WHERE 2 >= (SELECT COUNT(DISTINCT ConsultationFees)FROM PatientsCheckup P2
  WHERE P1.ConsultationFees >= P2.ConsultationFees) ORDER BY P1.ConsultationFees DESC;
 
– TWO MAXIMUM ConsultationFees
SELECT DISTINCT ConsultationFees FROM PatientsCheckup P1
 WHERE 2 >= (SELECT COUNT(DISTINCT ConsultationFees)FROM PatientsCheckup P2
  WHERE P1.ConsultationFees <= P2.ConsultationFees) ORDER BY P1.ConsultationFees DESC;

Q9. Write a query to remove duplicate rows in SQL.

DELETE FROM Patients WHERE PatientID IN (
SELECT 
ID, COUNT(PatientID) 
FROM   Patients
GROUP BY  PatientID
HAVING 
COUNT (PatientID) > 1); 

Q10. Write a SQL query to retrieve patient details from Patients table who have a weight in the PatientsCheckup table.

SELECT * FROM Patients P
WHERE EXISTS
(SELECT * FROM PatientsCheckup C WHERE P.PatientID = C.PatientID);

With this, we end this article on the subqueries in SQL. We hope you found it informative. 

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.