Database connection: Comparison between INNER JOIN and OUTER JOIN

In database operations, connection operations are the key to combining multiple table data. INNER JOIN and OUTER JOIN are two basic connection types. This article will explore their key differences and practical applications.

INNER JOIN: Intersection

INNER JOIN Select those rows that have matching values ​​in two or more columns in different tables. Conceptually, it gets the intersection of two sets, represented by the overlapping parts of the Venn diagram. Only rows that exist in both tables are included in the output.

OUTER JOIN: union

Unlike INNER JOIN, OUTER JOIN contains all rows in the specified table, regardless of whether they have matches in other tables. This operation effectively creates a union, represented by a combined area of ​​the Venn diagram.

OUTER JOIN Type

OUTER JOIN is divided into three types:

• LEFT OUTER JOIN: Gets all rows of the left table and any matching rows in the right table. Rows in the right table that have no matches in the left table will contain NULL values.
• RIGHT OUTER JOIN: Similar to LEFT OUTER JOIN, but gets all rows of the right table and matching rows in the left table.
• FULL OUTER JOIN: Combine all rows of two tables and fill in missing matches with NULL values.

Practical example

Consider the following table containing customer and their order data:

Customer (id, name)
Orders (id, customer_id, product)

INNER JOIN:

SELECT *
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id;

This query will only return customers who have placed the order.

LEFT OUTER JOIN:

SELECT *
FROM customer
LEFT OUTER JOIN orders ON customer.id = orders.customer_id;

This query will return all customers, including those without orders, and the order information will be NULL value.

RIGHT OUTER JOIN:

SELECT *
FROM customer
RIGHT OUTER JOIN orders ON customer.id = orders.customer_id;

This query will return all orders, including orders placed by customers that do not exist in the Customer table, and the customer information will be a NULL value.

FULL OUTER JOIN:

SELECT *
FROM customer
FULL OUTER JOIN orders ON customer.id = orders.customer_id;

This query will return all customers and all orders, filling in missing matches with NULL values.