What's the Difference Between INNER JOIN and OUTER JOIN in SQL?

Mastering SQL Joins: INNER JOIN and OUTER JOIN Explained

SQL joins are essential for combining data from multiple database tables. This guide focuses on two key join types: INNER JOIN and OUTER JOIN, highlighting their differences and applications.

INNER JOIN:

An INNER JOIN returns only the matching rows from two tables based on a specified join condition. It effectively filters out any rows where a match isn't found in both tables. The result set contains only data present in both joined tables.

OUTER JOIN:

Unlike INNER JOIN, OUTER JOINs return all rows from at least one of the tables. This includes rows with matching data in the other table, and crucially, rows without a match in the other table.

OUTER JOIN Variations:

Several types of OUTER JOIN exist, each with a specific purpose:

• LEFT OUTER JOIN (or LEFT JOIN): Returns all rows from the left table (the table specified before LEFT JOIN). If a match exists in the right table, the corresponding data is included; otherwise, NULL values are used for the right table columns.

• RIGHT OUTER JOIN (or RIGHT JOIN): Mirrors LEFT JOIN, but returns all rows from the right table and includes NULL values for unmatched rows in the left table.

• FULL OUTER JOIN (or FULL JOIN): Combines LEFT JOIN and RIGHT JOIN. It returns all rows from both tables. NULL values fill in where matches are missing in either table.

Illustrative Example:

Let's consider tables "A" and "B":

Table A	Table B
1	3
2	4
3	5
4	6

INNER JOIN (A INNER JOIN B ON A.a = B.b):

Table A	Table B
3	3
4	4

LEFT OUTER JOIN (A LEFT JOIN B ON A.a = B.b):

Table A	Table B
1	NULL
2	NULL
3	3
4	4

RIGHT OUTER JOIN (A RIGHT JOIN B ON A.a = B.b):

Table A	Table B
3	3
4	4
NULL	5
NULL	6

FULL OUTER JOIN (A FULL JOIN B ON A.a = B.b):

Table A	Table B
1	NULL
2	NULL
3	3
4	4
NULL	5
NULL	6

By understanding these join types, you can effectively query and manipulate data across multiple tables, gaining valuable insights from your database.

The above is the detailed content of What's the Difference Between INNER JOIN and OUTER JOIN in SQL?. For more information, please follow other related articles on the PHP Chinese website!