(query rows from a table that has matching rows in another table.)

The INNER JOIN matches each row in one table with every row in other tables and allows you to query rows that contain columns from both tables.
The INNER JOIN is an optional clause of the SELECT statement. It appears immediately after the FROM clause.
Here is the syntax of the INNER JOIN clause:
SELECT
Table1.column1,
Table2.column2
FROM
Table1
INNER JOIN Table2
ON Table1.common_column = Table2.common_column
WHERE
Table1.some_column = 'some_value'
ORDER BY
Table1.column1 DESCHere’s a step-by-step guide for creating a query with INNER JOIN, and adding conditions with WHERE and sorting with ORDER BY:
- Start with the Main Table: Begin with the main table (let's call it
Table1). This is the table you specify in theFROMclause, where most of your primary data will come from. - Specify the Joined Tables: List any other tables you need to join with the main table. Use the
INNER JOINkeyword to add each table (e.g.,Table2,Table3, etc.). - Define the Join Condition: After each
INNER JOIN, use theONkeyword to specify the common column that connects rows between the tables. The condition tells SQL how to match rows based on a common column (e.g.,Table1.id = Table2.id). - Filter Results with
WHERE(Optional): Use theWHEREclause to add any additional conditions that limit the rows in your result set. This could be filtering by a specific value, range, or condition (e.g.,WHERE Table1.status = 'Active'). - Sort the Results with
ORDER BY(Optional): To organize your results in a specific order, use theORDER BYclause. Specify the column(s) by which you want to sort and choose either ascending (ASC) or descending (DESC) order.
Summary
FROMINNER JOINONSELECT
Alternative to the on as a keywords is to use the using in combining the common keywords in both tables. Example
SELECT
Table1.Column,
Table2.Column,
FROM
Table1
INNER JOIN
Table2
Using (MatchingColumnName)MySQL INNER JOIN examples

Given a database with two tables, products and productlines, you need to retrieve details about each product and its associated product line description.
- The
productstable has columnsproductCode,productName, andproductline. - The
productlinestable has columnsproductlineandtextDescription. - The
productlinecolumn inproductsreferencesproductlineinproductlines, establishing a foreign key relationship.
Task: Write a query to return the following details for each product:
productCodefrom theproductstable.productNamefrom theproductstable.textDescriptionfrom theproductlinestable.
Requirements:
- Use an
INNER JOINto combine the tables based on theproductlinecolumn. - Try using both the
ONclause and theUSINGsyntax to accomplish the same result.
Expected Solution:
Using the ON clause:
SELECT
productCode,
productName,
textDescription
FROM
products t1
INNER JOIN
productlines t2
ON t1.productline = t2.productline;Using the USING syntax:
SELECT
productCode,
productName,
textDescription
FROM
products
INNER JOIN productlines USING (productline);
MySQL INNER JOIN with GROUP BY clause example

Question 2
Given a database with two tables, orders and orderdetails, write a query to find the total sales for each order, along with the order number and status.
- The
orderstable contains the columnsorderNumberandstatus. - The
orderdetailstable contains the columnsorderNumber,quantityOrdered, andpriceEach. - The
orderNumbercolumn inorderdetailsreferencesorderNumberinorders, forming a foreign key relationship.
Task
Write a query to return:
orderNumberfrom theorderstable.statusfrom theorderstable.- Total sales for each order, calculated as the sum of
quantityOrdered * priceEachfrom theorderdetailstable.
Requirements
- Use an
INNER JOINto combine theordersandorderdetailstables based on theorderNumbercolumn. - Group the results by
orderNumberto calculate the total for each order. - Use both the
ONclause and theUSINGsyntax to achieve the same result.
Expected Solution
Using the ON clause:
SELECT
t1.orderNumber,
t1.status,
SUM(t2.quantityOrdered * t2.priceEach) AS total
FROM
orders t1
INNER JOIN orderdetails t2
ON t1.orderNumber = t2.orderNumber
GROUP BY t1.orderNumber;Using the USING syntax:
SELECT
orderNumber,
status,
SUM(quantityOrdered * priceEach) AS total
FROM
orders
INNER JOIN orderdetails USING (orderNumber)
GROUP BY orderNumber;MySQL INNER JOIN – join three tables example
See the following products, orders and orderdetails tables:

Given a database with three tables, orders, orderdetails, and products, write a query to retrieve order information along with product details.
- The
orderstable contains columnsorderNumberandorderDate. - The
orderdetailstable contains columnsorderNumber,orderLineNumber,productCode,quantityOrdered, andpriceEach. - The
productstable contains columnsproductCodeandproductName. - The
orderNumbercolumn inorderdetailsreferencesorderNumberinorders. - The
productCodecolumn inorderdetailsreferencesproductCodeinproducts, forming foreign key relationships.
Task
Write a query to return:
orderNumberfrom theorderstable.orderDatefrom theorderstable.orderLineNumberfrom theorderdetailstable.productNamefrom theproductstable.quantityOrderedfrom theorderdetailstable.priceEachfrom theorderdetailstable.
Requirements
- Use two
INNER JOINclauses to join the three tables (orders,orderdetails, andproducts) based on their relationships. - Order the results by
orderNumberandorderLineNumber.
SELECT
orderNumber,
orderDate,
orderLineNumber,
productName,
quantityOrdered,
priceEach
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
INNER JOIN
products USING (productCode)
ORDER BY
orderNumber,
orderLineNumber;MySQL INNER JOIN – join four tables example
See the following orders, orderdetails, customers and products tables:

Question 4
Given a database with four tables, orders, orderdetails, products, and customers, write a query to retrieve detailed order information, including customer and product details.
- The
orderstable contains columnsorderNumber,orderDate, andcustomerNumber. - The
orderdetailstable contains columnsorderNumber,orderLineNumber,productCode,quantityOrdered, andpriceEach. - The
productstable contains columnsproductCodeandproductName. - The
customerstable contains columnscustomerNumberandcustomerName. - The
orderNumbercolumn inorderdetailsreferencesorderNumberinorders. - The
productCodecolumn inorderdetailsreferencesproductCodeinproducts. - The
customerNumbercolumn inordersreferencescustomerNumberincustomers.
Task
Write a query to return:
orderNumberfrom theorderstable.orderDatefrom theorderstable.customerNamefrom thecustomerstable.orderLineNumberfrom theorderdetailstable.productNamefrom theproductstable.quantityOrderedfrom theorderdetailstable.priceEachfrom theorderdetailstable.
Requirements
- Use three
INNER JOINclauses to join the four tables (orders,orderdetails,products, andcustomers) based on their relationships. - Order the results by
orderNumberandorderLineNumber.
SELECT
orderNumber,
orderDate,
customerName,
orderLineNumber,
productName,
quantityOrdered,
priceEach
FROM
orders
INNER JOIN orderdetails
USING (orderNumber)
INNER JOIN products
USING (productCode)
INNER JOIN customers
USING (customerNumber)
ORDER BY
orderNumber,
orderLineNumber;MySQL INNER JOIN using other operators
So far, you have seen that the join condition used the equal operator (=) for matching rows.
In addition to the equal operator (=), you can use other operators such as greater than ( >), less than ( <), and not-equal ( <>) operator to form the join condition.
The following query uses a less-than ( <) join to find the sales price of the product whose code is S10_1678 that is less than the manufacturer’s suggested retail price (MSRP) for that product.
Question 5
In previous examples, the join condition used the equal operator (=) to match rows. However, other operators can also be used, such as greater than (>), less than (<), and not-equal (<>) to form join conditions.
The following task involves using a less-than (<) join condition to find the sales price of a product that is below the manufacturer’s suggested retail price (MSRP).
- The
productstable contains columnsproductCode,productName, andmsrp. - The
orderdetailstable contains columnsorderNumber,productCode, andpriceEach. - The
productCodecolumn inorderdetailsreferencesproductCodeinproducts, forming a foreign key relationship.
Task
Write a query to return:
orderNumberfrom theorderdetailstable.productNamefrom theproductstable.msrpfrom theproductstable.priceEachfrom theorderdetailstable.
Requirements
- Use an
INNER JOINto combine theproductsandorderdetailstables based on theproductCodecolumn. - Include a join condition to filter for rows where the
msrpinproductsis greater than thepriceEachinorderdetails. - Use a
WHEREclause to filter the results to only show the product withproductCode'S10_1678'.
SELECT
orderNumber,
productName,
msrp,
priceEach
FROM
products p
INNER JOIN orderdetails o
ON p.productcode = o.productcode
AND p.msrp > o.priceEach
WHERE
p.productcode = 'S10_1678';