SQL JOINS

all about joins, and window functions , CTES, SUBQUERIES

Use INNER JOIN to get only customers who have placed at least one order. Tables: customers, orders.

What type of use case is this? why?

SELECT c.customer_id, c.name

FROM customers c

INNER JOIN orders o

ON c.customer_id = o.customer_id;

Filtering / Existence Check
Filters out customers who have no orders — only keeps rows where a match exists in the orders table.

Use INNER JOIN to get employee names and their department names from employees and departments tables
What type of use case is this? why?

SELECT

e.name AS employee_name,

d.department_name

FROM employees e

INNER JOIN departments d

ON e.department_id = d.department_id;

Recombine to get the full picture
Purpose: Recombines two tables to get a more complete view — pulling descriptive data (department_name) from another table.

What are the three main use cases for using SQL JOINS?

1) Recombine Data (big picture): use case to connect multiple tables to see the full picture of people in regions

2) Data Enrichment: getting extra data/info for customers and zip codes, extra data for the main table

3) Check for Extience: "fitlering" check if a customer placed an order -> look up in the orders table but doesn’t add filtering on a join

INNER JOIN
What does it match two ? Draw out the circules and explain its use cases ?

Only matching rows from two circles the data from a only if it existis in table b and vice versa doestn matter where you start (use case Recombine Data or Check Extience of rows)

LEFT JOIN (BASIC)
what is it match type draw with circles ?
what are the use cases for basics only?

Returns all rows from the left table and only matching from the right ADD

full circile on A only match on B meaning A is the Primary source of data and b is secondary need only for additonal data

-- order of the tables matter from clause

usecases : recombine data join and get extra infromation (data enerichment) (check extiencse left+where)

Write a query to list all employees and their department names, even if they don’t belong to any department.
what is the purpose of this join

SELECT

e.name AS employee_name,

d.department_name

FROM employees e

LEFT JOIN departments d

ON e.department_id = d.department_id;

Recombines data while preserving all employees. If an employee doesn’t have a matching department, department_name will be NULL.

Write a query to find all customers who have never placed an order. Tables: customers, orders. what is the purpose?

SELECT c.customer_id, c.name

FROM customers c

LEFT JOIN orders o

ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL;

Checks for non-existence — returns only customers not found in the orders table. The LEFT JOIN pulls all customers; the WHERE filters out the ones with matching orders.

Full OUTER JOINS ,What is full outer join? draw a circle to show case what it is and list out the use cases

returns all rows from both tables
use cases:
-) Recombine Data (big picture): use case to connect multiple tables to see the full picture of people in regions
- Check for Extience: "fitlering" check if a customer placed an order -> look up in the orders table but doesn’t add filtering on a join

Write a query to find people or orders that don't have a match in the other table.
Tables: people, orders. A person may have no orders; an order might be missing a valid person, what type of join and why?

SELECT

p.person_id, o.order_id

FROM people p

FULL OUTER JOIN orders o

ON p.person_id = o.person_id

WHERE p.person_id IS NULL OR o.order_id IS NULL;

🔍 Insight:

This shows rows that exist in one table but not the other — useful for spotting mismatches, missing references, or orphaned record

Write a query to list all students and all courses they are enrolled in, including:

• Students not enrolled in any course

• Courses with no enrolled students

Tables: students, enrollments, courses

SELECT

s.student_name,

c.course_name

FROM students s

FULL OUTER JOIN enrollments e

ON s.student_id = e.student_id

FULL OUTER JOIN courses c

ON e.course_id = c.course_id;

🔍 Insight:

This includes all students and all courses — even when there’s no enrollment link between them.