To use this website fully, you first need to accept the use of cookies. By agreeing to the use of cookies you consent to the use of functional cookies. For more information read this page.

Inner joins

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.

SQL
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:

SQL
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.

Provide feedback on this page
Comments are sent via email to me.