<sub>2025-05-17</sub> <sub>#data-visualization #data-management #sql #hmp669</sub>
<sup>[[maps-of-content|š Maps of Content ā All Notes]] </sup>
<sup>Series: [[hmp669|Data Management and Visualization]]</sup>
# SQL: Combining Multiple Clauses
> [!abstract]- Quick Review
>
> SQL clauses are building blocks that can be combined to create powerful, flexible queries that join multiple tables and filter data precisely.
>
> **Key Concepts**:
>
> - Multiple JOINs can be chained together to connect three or more tables
> - JOIN type (INNER vs LEFT) determines which rows appear in results
> - WHERE clause filters joined data based on specific conditions
>
> **Must Remember**:
>
> - INNER JOIN shows only rows with matches in ALL tables
> - LEFT JOIN preserves all rows from the left table, even without matches
> - Choose join types based on whether you need complete or partial data sets
>
> **Critical Relationships**:
>
> - JOIN connects tables based on related columns (keys)
> - Multiple JOINs create a chain of table relationships
> - WHERE filters operate on the combined result of all JOINs
> [!code]- Code Reference
>
>
> |Command/Syntax|Purpose|Example|
> |---|---|---|
> |**Table Joins**|||
> |INNER JOIN|Returns only rows with matches in both tables|`FROM table1 INNER JOIN table2 ON table1.id = table2.id`|
> |LEFT JOIN|Returns all rows from left table, matched rows from right|`FROM table1 LEFT JOIN table2 ON table1.id = table2.id`|
> |**Multiple Join Patterns**|||
> |Chained JOINs|Connect three or more tables|`FROM t1 JOIN t2 ON t1.id = t2.id JOIN t3 ON t2.id = t3.id`|
> |Mixed JOIN types|Combine INNER and LEFT joins|`FROM t1 LEFT JOIN t2 ON t1.id = t2.id INNER JOIN t3 ON t2.id = t3.id`|
> |**Filtering Operations**|||
> |WHERE with equality|Filter on specific values|`WHERE column_name = 'value'`|
> |WHERE with NULL|Filter for missing values|`WHERE column_name IS NULL`|
> |WHERE with OR|Filter with multiple conditions|`WHERE condition1 OR condition2`|
## Introduction: SQL Clauses as Building Blocks
SQL is a language of composition, where different clauses work together to retrieve precisely the data you need. Combining multiple SQL clausesāparticularly different types of joins with WHERE conditionsāto construct more complex and powerful queries.
SQL clauses like LEGO blocks: individually simple, but capable of creating sophisticated structures when combined thoughtfully. Understanding how these pieces fit together is essential for effective database querying.
## Joining Multiple Tables: Beyond Simple Connections
### The Concept of Table Chaining
While basic SQL often joins just two tables, real-world databases frequently require connections across multiple tables to bring together related information.
![[sql-combining-multiple-clauses-1747471205303.webp]]
### Three-Table Join Example
Consider a healthcare database with:
- **Patient table**: Contains patient information and a foreign key `physician_id`
- **Physician table**: Contains physician data and a foreign key `specialty_id`
- **Specialty table**: Contains medical specialties
To retrieve patient names, their physician names, and the physician's specialty:
```sql
SELECT
patient.first_name,
patient.last_name,
physician.physician_name,
specialty.specialty_name
FROM
patient
JOIN
physician ON patient.physician_id = physician.physician_id
JOIN
specialty ON physician.specialty_id = specialty.specialty_id;
```
> Each JOIN builds upon the previous one. The first JOIN connects patients to physicians, then the second JOIN adds specialty information to those combined results
## The Impact of Join Types
### INNER JOIN: The Strict Connector
When using INNER JOIN across multiple tables, only rows with matches in **all** joined tables appear in the results.
> [!caution] Important Limitation
> Using INNER JOINs exclusively means you'll only see data where there's a complete chain of relationships. Any missing link (NULL foreign key or no matching record) will cause the entire row to be excluded.
```mermaid
graph LR
subgraph "INNER JOIN Results"
A((Patient)) & B((Physician)) & C((Specialty)) --- D[Only records<br>with matches<br>in ALL tables]
end
```
### LEFT JOIN: The Inclusive Connector
LEFT JOIN preserves all rows from the left table, regardless of whether there are matches in the right table.
#### Example: Getting All Patients With or Without Physicians
```sql
SELECT
patient.first_name,
patient.last_name,
physician.physician_name
FROM
patient
LEFT JOIN
physician ON patient.physician_id = physician.physician_id;
```
This query returns all patients, even those without an assigned physician (where physician fields will be NULL).
#### Example: Chaining Multiple LEFT JOINs
To get all patients (regardless of physician) and all physician information (regardless of specialty):
```sql
SELECT
patient.first_name,
patient.last_name,
physician.physician_name,
specialty.specialty_name
FROM
(patient
LEFT JOIN
physician ON patient.physician_id = physician.physician_id)
LEFT JOIN
specialty ON physician.specialty_id = specialty.specialty_id;
```
> [!note] LEFT JOIN Effect
> The first LEFT JOIN ensures all patients appear in results. The second LEFT JOIN ensures all patient-physician combinations appear, regardless of whether the physician has a specialty.
## Integrating WHERE Clause with Joins
The WHERE clause filters the combined dataset after all joins have been processed.
### Filtering Joined Results by Value
To get patients, physicians and specialties, but only for a specific physician:
```sql
SELECT
patient.first_name,
patient.last_name,
physician.physician_name,
specialty.specialty_name
FROM
(patient
JOIN
physician ON patient.physician_id = physician.physician_id)
JOIN
specialty ON physician.specialty_id = specialty.specialty_id
WHERE
physician.physician_name = 'Zhe Zhang';
```
### Filtering for NULL Values
You can use the WHERE clause with IS NULL to find records with missing relationships:
```sql
SELECT
patient.first_name,
patient.last_name,
physician.physician_name,
specialty.specialty_name
FROM
(patient
LEFT JOIN
physician ON patient.physician_id = physician.physician_id)
LEFT JOIN
specialty ON physician.specialty_id = specialty.specialty_id
WHERE
physician.physician_id IS NULL OR specialty.specialty_id IS NULL;
```
This query finds patients without an assigned physician OR physicians without a specialty.
## Strategic Join Selection: Choosing the Right Tool
Selecting the appropriate join type is crucial for getting the results you need. Consider these questions:
1. **Do you need all records from a certain table?**
- If yes, that table should be on the LEFT side of a LEFT JOIN
2. **Are you only interested in complete relationships?**
- If yes, INNER JOIN is appropriate
3. **Is the presence of NULL values meaningful to your analysis?**
- If yes, use LEFT JOIN and possibly filter with WHERE IS NULL
> [!example]- Case Application: Patient Management System
>
> A hospital needs three reports from their database:
>
> **1. Complete Patient-Physician-Specialty Report**
>
>
> ```sql
> SELECT
> patient.first_name,
> patient.last_name,
> physician.physician_name,
> specialty.specialty_name
> FROM
> (patient
> INNER JOIN
> physician ON patient.physician_id = physician.physician_id)
> INNER JOIN
> specialty ON physician.specialty_id = specialty.specialty_id;
> ```
>
> _This shows only patients with complete information linkages._
>
> **2. Unassigned Patient Report**
>
> ```sql
> SELECT
> patient.first_name,
> patient.last_name
> FROM
> patient
> LEFT JOIN
> physician ON patient.physician_id = physician.physician_id
> WHERE
> physician.physician_id IS NULL;
> ```
>
> _This identifies patients without an assigned physician._
>
> **3. Specialty Coverage Report**
>
>
> ```sql
> SELECT
> patient.first_name,
> patient.last_name,
> physician.physician_name,
> specialty.specialty_name
> FROM
> (patient
> LEFT JOIN
> physician ON patient.physician_id = physician.physician_id)
> LEFT JOIN
> specialty ON physician.specialty_id = specialty.specialty_id
> ORDER BY
> specialty.specialty_name;
> ```
>
> _This shows all patients with their physician and specialty information (if available), organized by specialty._
## Summary: The Power of Combined Clauses
SQL clauses are not isolated operations but interconnected building blocks.
1. **Joins connect tables** based on related columns, allowing you to bring together data from multiple sources
2. **Join type determines inclusion** - INNER JOIN requires matches, LEFT JOIN preserves all rows from the left table
3. **Multiple joins can be chained** to connect three or more tables in a single query
4. **WHERE clauses filter after joining**, allowing you to narrow down the combined dataset
5. **Strategic join selection** is critical for getting exactly the data you need
--
Reference:
- HMP 669