SQL Inner Join – The Ins and Outs of SQL Inner Join

An Inner Join in SQL is a method to combine rows from two or more tables based on a related column

SQL, or Structured Query Language, provides us with several ways to combine data from different tables. One of the essential methods to achieve this is by using an Inner Join.

SQL Inner Join Syntax

An Inner Join in SQL is a method to combine rows from two or more tables based on a related column. Let’s start by exploring its syntax

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

This command creates a new table that includes rows where the join condition is true.

Unpacking the Syntax with Examples

To make this clearer, let’s take a look at an example. I will use two simple tables, Orders and Customers.

Here’s the Orders table:

| OrderID | CustomerID | Product    |
|---------|------------|------------|
| 1       | 101        | Apples     |
| 2       | 102        | Bananas    |
| 3       | 103        | Cherries   |
| 4       | 104        | Dates      |
| 5       | 105        | Eucalyptus |

And the Customers table:

| CustomerID | Name     | Country  |
|------------|----------|----------|
| 101        | Alice    | USA      |
| 102        | Bob      | UK       |
| 103        | Charlie  | Canada   |
| 104        | David    | Australia|

Our goal is to create a new table that combines the Name from the Customers table with OrderID and Product from the Orders table, for each matching CustomerID.

To achieve this, I’ll use the Inner Join syntax as follows:

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

Executing this command gives us a new table:

| OrderID | Name     | Product   |
|---------|----------|-----------|
| 1       | Alice    | Apples    |
| 2       | Bob      | Bananas   |
| 3       | Charlie  | Cherries  |
| 4       | David    | Dates     |

This result set only includes the customers who have an order and their respective orders, according to the matching CustomerID.

Wrapping Up

Understanding and using Inner Join operation is key to efficient SQL data handling. It enables you to gather and correlate data from disparate tables based on common attributes.