Why do I have more rows after LEFT JOIN?

How can a LEFT OUTER JOIN return more records than exist in the left table?

To "get as many records returned as exist in the left table", you need to specify which row from the right side to choose if there are multiple matches.

回答

The LEFT OUTER JOIN will return all records from the LEFT table joined with the RIGHT table where possible.

If there are matches though, it will still return all rows that match, therefore, one row in LEFT that matches two rows in RIGHT will return as two ROWS, just like an INNER JOIN.

EDIT: In response to your edit, I've just had a further look at your query and it looks like you are only returning data from the LEFT table. Therefore, if you only want data from the LEFT table, and you only want one row returned for each row in the LEFT table, then you have no need to perform a JOIN at all and can just do a SELECT directly from the LEFT table.

左表里面的一条数据，可能关联了右边的多条数据

回答2

In response to your postscript, that depends on what you would like.

You are getting (possible) multiple rows for each row in your left table because there are multiple matches for the join condition. If you want your total results to have the same number of rows as there is in the left part of the query you need to make sure your join conditions cause a 1-to-1 match.

Alternatively, depending on what you actually want you can use aggregate functions (if for example you just want a string from the right part you could generate a column that is a comma delimited string of the right side results for that left row.

If you are only looking at 1 or 2 columns from the outer join you might consider using a scalar subquery since you will be guaranteed 1 result.