SUBQUERIES

It is a query inside a query and are normally enclosed inside parentheses ()

They help break down complex queries into manageable parts.

Used in Insert, Update, Delete statements

• SELECT CLAUSE

• FROM CLAUSE

• WHERE CLAUSE

• HAVING CLAUSE

Why Subqueries

1. They help simplify complex sql statements by breaking them into smaller easier to understand components.

2. They filter data dynamically

• Subqueries calculate intermediate results that are required for further analysis in the main query.

SUBQUERY IN THE SELECT CLAUSE

They are used to add an extra column to the result set by calculating a value based on another query.

Add calculated value to the result

Total visits per patient

select first_name,last_name,
        (select count (*) from mental_health_visits where patient_id = mental_health_patients.patient_id) as 
total_visits
from mental_health_patients;

The above query counts the number of visits for each patient. Outer query retrieves the patient names and the result of the subquery is shown as the alias total visits for each patient.

SUBQUERIES IN THE FROM CLAUSE

Used to create a temporary table (a derived table) which the outer query can use.

Count Visits per diagnosis

select diagnosis, total_visits
from(
      select diagnosis, count(*) as total_visits
      from mental_health_patients
      group by diagnosis
) as diagnosis_visits;

Outer query retrieves the diagnosis and total visit count.

SUBQUERIES IN THE WHERE CLAUSE

Used to filter records based on the result of another query

Patients who visited a psychologist

SELECT first_name, last_name
FROM mental_health_patients
WHERE patient_id IN (
 SELECT patient_id
 FROM mental_health_visits
 JOIN mental_health_doctors ON mental_health_visits.doctor_id =
mental_health_doctors.doctor_id
 WHERE mental_health_doctors.specialization = 'Psychologist'

The subquery finds the patient_id for all the patients who visited a psychologist. The outer query filters the patients based on this list of patient_ids.

CORRELATED SUBQUERIES

Entails row by row processing.

Subquery that references columns from the outer query.

Subquery is executed for each row processed by the outer query.

Patients with more than one emergency visit

SELECT first_name, last_name
FROM mental_health_patients p
WHERE (
 SELECT COUNT(*)
 FROM mental_health_visits v
 WHERE v.patient_id = p.patient_id AND v.visit_type = 'Emergency'
) > 1;

The subquery counts the number of emergency visits for each patient.

v.patient_id = p.patient_id condition makes this a correlated subquery

• The outer query returns the names of patients with more than 1 emergency visit.

Subqueries are powerful but can be less efficient with large datasets. For better performance use joins when appropriate, try limitting the number of nested subqueries