(INNER) JOIN

An INNER JOIN is a type of join that fetches rows from two tables where the join condition is met. In other words, INNER JOIN returns only the records where there is a match in both tables. Here is a Venn diagram representation of an INNER JOIN:

When using INNER JOIN, the syntax goes like this:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Let’s consider a real-world example:

Suppose we have two tables, Orders and Customers:

Orders

OrderID  | CustomerID  | Product    | Quantity
-------- | ----------- | ---------- | --------
1        | 3           | Book       | 2
2        | 1           | Pen        | 3
3        | 2           | Notebook   | 1
4        | 5           | Pencil     | 4
5        | 1           | Eraser     | 1

Customers

CustomerID | CustomerName
---------- | ------------
1          | John
2          | Maria
3          | Steve
4          | Michael

Example Test Question

Prepare a list that shows the order ID, the product, the quantity of the product, and the name of the customer who ordered each product. Only include customers and orders that exist in both tables.

Answer

To solve this, we need to do an INNER JOIN on the Orders and Customers tables. We would join them based on the CustomerID which is common in both tables.

Here is the SQL query to achieve this:

SELECT Orders.OrderID, Orders.Product, Orders.Quantity, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

This would return the following result:

OrderID  | Product  | Quantity | CustomerName
-------- | -------- | -------- | ------------
1        | Book     | 2        | Steve
2        | Pen      | 3        | John
3        | Notebook | 1        | Maria
5        | Eraser   | 1        | John

In the result, you can see that only those records where the CustomerID was found in both tables are included. You will notice that OrderID 4 is not in the result set because there is no customer with a CustomerID of 5 in the Customers table, so it does not satisfy the condition for an INNER JOIN.

This is how an INNER JOIN works in SQL, providing a result set that includes only records where a match is found in both tables according to the join condition.