SQL INNER JOIN
An SQL INNER JOIN is used to combine rows from two or more tables based on a related column between them. It returns only the rows that have matching values in both tables.
Let's illustrate this with an example. Consider two tables: Customers
and Orders
.
Table: Customers
CustomerID |
CustomerName |
Country |
1 |
Alice |
USA |
2 |
Bob |
Canada |
3 |
Charlie |
UK |
Table: Orders
OrderID |
CustomerID |
OrderDate |
101 |
1 |
2023-01-01 |
102 |
2 |
2023-01-02 |
103 |
1 |
2023-01-03 |
104 |
4 |
2023-01-04 |
SQL INNER JOIN Query
To find all orders with the corresponding customer names, you can use the following INNER JOIN query:
SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Output of the Query
CustomerID |
CustomerName |
OrderID |
OrderDate |
1 |
Alice |
101 |
2023-01-01 |
1 |
Alice |
103 |
2023-01-03 |
2 |
Bob |
102 |
2023-01-02 |
Explanation
- FROM Customers: The query starts with the
Customers
table.
- INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID: The query combines the
Customers
table with the Orders
table using the CustomerID
column. The INNER JOIN
ensures that only the rows with matching CustomerID
values in both tables are included in the result.
- SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate: This specifies the columns to be included in the output.
Why Some Rows Are Excluded
- The
Orders
table has an OrderID
of 104 with a CustomerID
of 4, but there is no CustomerID
of 4 in the Customers
table. Thus, this row is excluded from the result.
- Similarly, if there were any customers without orders, they would also be excluded because the INNER JOIN requires a match in both tables.
This is a basic example to demonstrate the concept of an INNER JOIN. You can extend this to more complex scenarios involving multiple tables and conditions.