SQL多表查询：两表Join查询及三表关联获取title与image_path

Hey there! Let's tackle your SQL questions step by step:

1. How to implement SELECT JOIN queries for two tables #

Joining two tables revolves around linking them via a shared key (usually a foreign key that references a primary key in another table). Here are the most common join types you’ll use, with straightforward examples:

• INNER JOIN: Returns only rows where matching records exist in both tables.

  SELECT table1.column_name, table2.column_name
  FROM table1
  INNER JOIN table2 ON table1.common_key = table2.common_key;
  

  This is the go-to join for filtering out rows that don’t have a match on both sides.

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, plus any matching rows from the right table. If there’s no match, the right table’s columns will show NULL.

  SELECT table1.column_name, table2.column_name
  FROM table1
  LEFT JOIN table2 ON table1.common_key = table2.common_key;
  

  Use this when you need to retain all records from your primary table, even if there’s no related data in the secondary table.

• RIGHT JOIN (or RIGHT OUTER JOIN): The reverse of a LEFT JOIN—returns all rows from the right table, plus matching rows from the left table. Unmatched columns from the left table will be NULL.

  SELECT table1.column_name, table2.column_name
  FROM table1
  RIGHT JOIN table2 ON table1.common_key = table2.common_key;
  

• FULL JOIN (or FULL OUTER JOIN): Returns all rows from both tables, matching where possible. Unmatched rows from either table will have NULL values in the opposite table’s columns. Note that some databases (like MySQL) don’t support FULL JOIN natively, but you can simulate it with a UNION of LEFT and RIGHT JOINs.

  SELECT table1.column_name, table2.column_name
  FROM table1
  FULL JOIN table2 ON table1.common_key = table2.common_key;
  

2. SQL query to fetch title and image_path from your three tables #

Given your table structures:

• users: id | title
• relation: id | image_id | user_id (junction table linking users to images)
• images: id | image_path

You’ll need to join all three tables using their foreign key relationships. Here are two common scenarios:

Scenario 1: Only return users with associated images (INNER JOIN) #

This query will only show rows where a user has at least one linked image:

SELECT u.title, i.image_path
FROM users u
INNER JOIN relation r ON u.id = r.user_id
INNER JOIN images i ON r.image_id = i.id;

We use aliases (u for users, r for relation, i for images) to keep the query concise and readable.

Scenario 2: Return all users, even those without images (LEFT JOIN) #

If you want to include every user in the results (with NULL for image_path if they have no linked images), use LEFT JOINs instead:

SELECT u.title, i.image_path
FROM users u
LEFT JOIN relation r ON u.id = r.user_id
LEFT JOIN images i ON r.image_id = i.id;

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