Search

INNER JOIN
INNER JOIN

INNER JOIN

Multi-select
Completed
Status
Done

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

image

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 DESC

Here’s a step-by-step guide for creating a query with INNER JOIN, and adding conditions with WHERE and sorting with ORDER BY:

  1. Start with the Main Table: Begin with the main table (let's call it Table1). This is the table you specify in the FROMclause, where most of your primary data will come from.
  2. Specify the Joined Tables: List any other tables you need to join with the main table. Use the INNER JOIN keyword to add each table (e.g., Table2, Table3, etc.).
  3. Define the Join Condition: After each INNER JOIN, use the ON keyword 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).
  4. Filter Results with WHERE (Optional): Use the WHERE clause 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').
  5. Sort the Results with ORDER BY (Optional): To organize your results in a specific order, use the ORDER BY clause. Specify the column(s) by which you want to sort and choose either ascending (ASC) or descending (DESC) order.

Summary

  1. FROM
  2. INNER JOIN
  3. ON
  4. SELECT

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

image

Given a database with two tables, products and productlines, you need to retrieve details about each product and its associated product line description.

  • The products table has columns productCode, productName, and productline.
  • The productlines table has columns productline and textDescription.
  • The productline column in products references productline in productlines, establishing a foreign key relationship.

Task: Write a query to return the following details for each product:

  1. productCode from the products table.
  2. productName from the products table.
  3. textDescription from the productlines table.

Requirements:

  • Use an INNER JOIN to combine the tables based on the productline column.
  • Try using both the ON clause and the USING syntax 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

image

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 orders table contains the columns orderNumber and status.
  • The orderdetails table contains the columns orderNumber, quantityOrdered, and priceEach.
  • The orderNumber column in orderdetails references orderNumber in orders, forming a foreign key relationship.

Task

Write a query to return:

  • orderNumber from the orders table.
  • status from the orders table.
  • Total sales for each order, calculated as the sum of quantityOrdered * priceEach from the orderdetails table.

Requirements

  1. Use an INNER JOIN to combine the orders and orderdetails tables based on the orderNumber column.
  2. Group the results by orderNumber to calculate the total for each order.
  3. Use both the ON clause and the USING syntax 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:

image

Given a database with three tables, orders, orderdetails, and products, write a query to retrieve order information along with product details.

  • The orders table contains columns orderNumber and orderDate.
  • The orderdetails table contains columns orderNumber, orderLineNumber, productCode, quantityOrdered, and priceEach.
  • The products table contains columns productCode and productName.
  • The orderNumber column in orderdetails references orderNumber in orders.
  • The productCode column in orderdetails references productCode in products, forming foreign key relationships.

Task

Write a query to return:

  • orderNumber from the orders table.
  • orderDate from the orders table.
  • orderLineNumber from the orderdetails table.
  • productName from the products table.
  • quantityOrdered from the orderdetails table.
  • priceEach from the orderdetails table.

Requirements

  1. Use two INNER JOIN clauses to join the three tables (orders, orderdetails, and products) based on their relationships.
  2. Order the results by orderNumber and orderLineNumber.
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:

image

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 orders table contains columns orderNumber, orderDate, and customerNumber.
  • The orderdetails table contains columns orderNumber, orderLineNumber, productCode, quantityOrdered, and priceEach.
  • The products table contains columns productCode and productName.
  • The customers table contains columns customerNumber and customerName.
  • The orderNumber column in orderdetails references orderNumber in orders.
  • The productCode column in orderdetails references productCode in products.
  • The customerNumber column in orders references customerNumber in customers.

Task

Write a query to return:

  • orderNumber from the orders table.
  • orderDate from the orders table.
  • customerName from the customers table.
  • orderLineNumber from the orderdetails table.
  • productName from the products table.
  • quantityOrdered from the orderdetails table.
  • priceEach from the orderdetails table.

Requirements

  1. Use three INNER JOIN clauses to join the four tables (orders, orderdetails, products, and customers) based on their relationships.
  2. Order the results by orderNumber and orderLineNumber.
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 products table contains columns productCode, productName, and msrp.
  • The orderdetails table contains columns orderNumber, productCode, and priceEach.
  • The productCode column in orderdetails references productCode in products, forming a foreign key relationship.

Task

Write a query to return:

  • orderNumber from the orderdetails table.
  • productName from the products table.
  • msrp from the products table.
  • priceEach from the orderdetails table.

Requirements

  1. Use an INNER JOIN to combine the products and orderdetails tables based on the productCode column.
  2. Include a join condition to filter for rows where the msrp in products is greater than the priceEach in orderdetails.
  3. Use a WHERE clause to filter the results to only show the product with productCode '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';