请求提供inner join、left/right join等连接的三类适用场景实际案例

Great question—those join logic scenarios can be tricky to ground in real life, so let’s break this down with relatable examples that your students will actually connect with!

案例A：Inner Join（精准匹配，只留交集） #

Let’s use an e-commerce scenario: Suppose you have two tables:

• orders: Contains all customer orders (with customer_id, order_date, total_amount)
• payments: Contains all successful payments (with customer_id, payment_date, amount_paid)

Your goal is to find every customer who placed an order AND completed a payment, along with their order and payment details.

Why Inner Join? Because we only care about records where a customer_id exists in both tables. Any customer who placed an order but never paid, or paid without an order (unlikely here, but possible), gets excluded. This is perfect for answering "what's the overlap between these two datasets?"

You’d write something like:

SELECT o.customer_id, o.order_date, p.payment_date
FROM orders o
INNER JOIN payments p ON o.customer_id = p.customer_id;

案例B：Left/Right Join（保留一侧全部，匹配另一侧） #

Let’s switch to a university setting: You have:

• students: All enrolled students (with student_id, name, major)
• course_enrollments: Students who signed up for courses (with student_id, course_code, enroll_date)

Your goal is to generate a full list of all students, and show which courses they’re enrolled in—including students who haven’t signed up for any courses yet.

Why Left Join? We want to keep every record from the students table (the "left" table), and match it to any corresponding enrollment records. Students with no enrollments will just show NULL for the course columns.

If your goal was instead to list all courses, including those that no students have enrolled in, you’d use a Right Join (swap the tables, or use Left Join with course_enrollments as the left table).

Example query for the student-focused task:

SELECT s.student_id, s.name, ce.course_code
FROM students s
LEFT JOIN course_enrollments ce ON s.student_id = ce.student_id;

案例C：Full Join（保留两侧全部，展示所有交集与独有的记录） #

Let’s use a customer data merge scenario: You have two customer lists from different channels:

• online_customers: People who signed up via your website (with email, signup_date, source)
• in_store_customers: People who signed up in physical stores (with email, signup_date, store_location)

Your goal is to create a single master list of all customers, showing which channel(s) they signed up through. This means you want to include:

• Customers who signed up both online and in-store
• Customers who only signed up online
• Customers who only signed up in-store

Why Full Join? It preserves every record from both tables, matching where possible (by email), and filling in NULL for columns where there’s no match. This is ideal when you don’t want to lose any data from either dataset.

Example query:

SELECT 
  COALESCE(oc.email, isc.email) AS customer_email,
  oc.signup_date AS online_signup,
  isc.signup_date AS in_store_signup
FROM online_customers oc
FULL JOIN in_store_customers isc ON oc.email = isc.email;

内容的提问来源于stack exchange，提问作者I Like to Code