Inner Join versus Outer Join

Inner Join versus Outer Join, A SQL Server inner join is a type of join that combines records from two or more tables in a database based on the values of the joined columns from each table. Inner joins only return rows that have matching values in the joined columns. In contrast, an outer join will return all rows from one table, and any matching rows from the other table(s).

Inner joins are useful when you want to return only the records that have a match in both tables, while outer joins can be useful when you want to include records from one table even if there is no matching record in the other table(s).

Here are some images that show generic examples of how an INNER JOIN versus OUTER JOIN keep in mind that you need to specify the type of outer join:

The inner join shows those rows that match on the join predicate between 2 or more tables.

The outer join (left or right) shows those that match plus those that aren’t matched from one table.

For example, let’s say you have a customers table and an orders table. An inner join between these two tables would return only those customers who have placed an order, while an outer join could return all customers, including those who have not placed any orders.

Here’s an example of a SQL query that uses an inner join:

SELECT *

FROM customers

INNER JOIN orders ON customers.customer_id = orders.customer_id

And here’s an example of a query that uses an outer join:

SELECT *

FROM customers

LEFT JOIN orders ON customers.customer_id = orders.customer_id

The main difference between the two queries is the use of the INNER JOIN and LEFT JOIN keywords, which specify the type of join to use. The inner join returns only the rows that have matching values in the joined columns, while the outer join returns all rows from the left table (customers) and any matching rows from the right table (orders).

Want to learn more about join types?

http://SteveStedman.com/joins

Joins Class available at : https://stedmans-sql-school.teachable.com/p/tsql-join-types

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!