Explore BrainMass

Types of Join Operations

What are the different types of joins, and how are they used? Show examples to support your reasoning.

Solution Preview

A join is an operation that allows you to query two or more tables to produce a single result set that incorporates rows and columns from each table. You join tables based on columns in each table that contain common data values.

There are three types of joins: inner joins, outer joins, and cross joins. Additionally, you can join more than two tables by using a series of joins within a SELECT statement, or you can join a table to itself by using a self-join.

1. Inner joins combine tables by comparing values in columns that are common to both tables.

SELECT buyer_name, sales.buyer_id, qty
FROM buyers INNER JOIN sales
ON buyers.buyer_id = sales.buyer_id

This example returns the buyer_name, buyer_id, and qty values for the buyers who purchased products. Buyers who did not purchase any products are not included in the result set. Buyers who bought more than one product are listed for each purchase.

2. Like inner joins, outer joins return combined rows that match the join condition. However, in addition to the rows that match the join condition, left and right outer joins return unmatched rows ...

Solution Summary

800+ words outline the 3 types of join operations and when you would use each.