Inner join with the an example
An inner join is a type of join in SQL that returns rows from two or more tables that match on one or more columns. If there's no match, the rows will not be included in the result set. It's a common way to combine data from related tables based on a common column.
Here is an example to illustrate an inner join:
Let's say we have two tables: `Customers` and `Orders`.
`Customers` table:
| CustomerID | CustomerName |
|------------|--------------|
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Alice Brown |
`Orders` table:
| OrderID | CustomerID | OrderDate |
|---------|------------|------------|
| 100 | 1 | 2023-04-01 |
| 101 | 2 | 2023-04-03 |
| 102 | 1 | 2023-04-04 |
Assume we want to get a list of all orders along with the names of the customers who made them. We can perform an inner join on these tables using the `CustomerID` column, which is the common column between these two tables.
Here's the SQL statement to perform an inner join:
```sql
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
```
This query will result in the following data:
| CustomerName | OrderID | OrderDate |
|--------------|---------|------------|
| John Doe | 100 | 2023-04-01 |
| Jane Smith | 101 | 2023-04-03 |
| John Doe | 102 | 2023-04-04 |
Explanation of the query:
- `SELECT` indicates which columns we want to retrieve.
- `FROM Customers` specifies the first table we're selecting data from.
- `INNER JOIN Orders` tells the database to perform an inner join with the `Orders` table.
- `ON Customers.CustomerID = Orders.CustomerID` is the condition used to match rows from both tables. Only the rows with equal `CustomerID` values from both tables will appear in the result.
As seen in the result set, only customers with matching orders are listed, and the order details are combined with the customer names. If there are customers without orders or orders without matching customer records, they will not be included in the result set of an inner join.