基于映射表users_jobs实现users表与jobs表的多条件SQL连接求助

Got it, let's walk through how to connect your users and jobs tables using the users_jobs mapping table. First, let's recap the table structures you shared to make sure we're on the same page:

Users Table #

Jobs Table #

First, I’ll assume your users_jobs junction table follows the standard many-to-many pattern—meaning it has at least two columns: user_id (foreign key linking to users.id) and job_id (foreign key linking to jobs.id). This table acts as the bridge between the two tables since a user can hold multiple jobs, and a job can be assigned to multiple users.

Basic Inner Join (Only Users with Assigned Jobs) #

If you only want to see users who have at least one job assigned, use an inner join. This will return only the user-job pairs that exist in the users_jobs table:

SELECT 
  u.id AS user_id,
  u.name AS user_name,
  j.id AS job_id,
  j.title AS job_title
FROM users u
JOIN users_jobs uj ON u.id = uj.user_id
JOIN jobs j ON uj.job_id = j.id;

Left Join (Include All Users, Even Those Without Jobs) #

If you want to retain every user in the results—even if they don’t have any jobs assigned—swap to left joins. Users without jobs will show NULL values for the job-related columns:

SELECT 
  u.id AS user_id,
  u.name AS user_name,
  j.id AS job_id,
  j.title AS job_title
FROM users u
LEFT JOIN users_jobs uj ON u.id = uj.user_id
LEFT JOIN jobs j ON uj.job_id = j.id;

Right Join (Include All Jobs, Even Those Without Assigned Users) #

Conversely, if you want to keep all jobs in the results (even if no user is assigned to them), use right joins. Jobs without assigned users will have NULL for user-related columns:

SELECT 
  u.id AS user_id,
  u.name AS user_name,
  j.id AS job_id,
  j.title AS job_title
FROM users u
RIGHT JOIN users_jobs uj ON u.id = uj.user_id
RIGHT JOIN jobs j ON uj.job_id = j.id;

内容的提问来源于stack exchange，提问作者Julius