Inner joins focus on matching rows between two tables. Inner join is the most basic form of join in SQL.
Inner Joins in SQL
The INNER JOIN keywords are used to signal an inner join in SQL. The JOIN can be used instead of the INNER JOIN
keyword and serve exactly the same purpose.
An inner join is the intersection between two tables since it requires that the elements (rows) that match are in both.
The following tables are used in these examples:
Product_Info
| product_id | product_name |
|---|---|
| 001 | Intel Core i7 2600 |
| 002 | Intel Core i5 2400 |
| 003 | Intel Core i3 2120 |
Purchases
| product_id | price |
|---|---|
| 001 | $270.00 |
| 001 | $252.00 |
| 002 | $120.00 |
| 002 | $190.00 |
Assuming these tables are for an online auction website, where prices will vary, then a query could be used to find a list of all the names of products purchased.
SELECT `product_name`, `price` FROM `Purchases` p INNER JOIN `Product_Info` i ON p.`product_id` = i.`product_id`
The results table will look like:
| product_name | price |
|---|---|
| Intel Core i7 2600 | $270.00 |
| Intel Core i7 2600 | $252.00 |
| Intel Core i5 2400 | $120.00 |
| Intel Core i5 2400 | $190.00 |
The result table has been formed as a combination of both tables, collecting the useful information needed from both tables. This is useful since it generates a table with information that is easily readable, since product IDs are not useful to a human user, but the names of the products are. It also saves a second query to request data from another table.
Equi-joins
The same statement can be written as an equi-join
without using the INNER keyword as shown below:
SELECT `product_name`, `price` FROM `Purchases` `Product_Info` WHERE `Purchases`.`product_id` = `Products`.`product_id`
An equi-join does the join within the WHERE part
of the statement and therefore is slower than using an inner join.
