MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL INNER JOIN: inner join

The INNER JOIN keyword in MySQL is used to combine rows from two or more tables based on a related column between them. It returns rows that have matching values in both tables.

Here's the basic syntax of an INNER JOIN:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

For example, suppose we have two tables: Orders and Customers, with the following structure:

Orders table:

| OrderID | CustomerID | OrderDate |
|---------|------------|-----------|
|       1 |          3 | 2023-01-01|
|       2 |          1 | 2023-02-02|
|       3 |          2 | 2023-03-03|

Customers table:

| CustomerID | CustomerName |
|------------|--------------|
|          1 | John         |
|          2 | Jane         |
|          3 | Alice        |

You could use an INNER JOIN to combine these tables based on the CustomerID column like this:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

This would return:

| OrderID | CustomerName | OrderDate |
|---------|--------------|-----------|
|       1 | Alice        | 2023-01-01|
|       2 | John         | 2023-02-02|
|       3 | Jane         | 2023-03-03|

This result set contains each OrderID, along with the CustomerName for the customer who placed the order, and the OrderDate. Note that it only includes orders where the CustomerID in the Orders table matches a CustomerID in the Customers table. If an order had a CustomerID that wasn't in the Customers table, that order wouldn't appear in the result set. Similarly, if a customer didn't have any orders, that customer wouldn't appear in the result set. That's why it's called an INNER JOIN - it only returns rows where there's a match in both tables.

  1. MySQL INNER JOIN example:

    • Description: The INNER JOIN combines rows from two or more tables based on a specified condition, excluding non-matching rows.
    • Example:
      SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

  2. How to use INNER JOIN in MySQL:

    • Description: Use the INNER JOIN keyword to retrieve rows from tables that have matching values in specified columns.
    • Example:
      SELECT employees.employee_id, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

  3. Inner joining tables in MySQL:

    • Description: Inner join combines rows from two tables based on a common column, creating a result set with matched values.
    • Example:
      SELECT products.product_name, categories.category_name
FROM products
INNER JOIN categories ON products.category_id = categories.category_id;

  4. Joining multiple tables using INNER JOIN in MySQL:

    • Description: Extend INNER JOIN to join multiple tables, linking them based on specified conditions.
    • Example:
      SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;

  5. Filtering INNER JOIN results in MySQL:

    • Description: Apply additional conditions in the WHERE clause to filter INNER JOIN results based on specific criteria.
    • Example:
      SELECT employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
WHERE employees.salary > 50000;

  6. MySQL INNER JOIN vs LEFT JOIN:

    • Description: INNER JOIN returns only matched rows, while LEFT JOIN returns all rows from the left table and matched rows from the right table.
    • Example with INNER JOIN:
      SELECT customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
    • Example with LEFT JOIN:
      SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

  7. Examples of using INNER JOIN in MySQL queries:

    • Description: Demonstrate various use cases of the INNER JOIN statement in MySQL queries.
    • Examples:
      SELECT employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
SELECT users.username, roles.role_name
FROM users
INNER JOIN user_roles ON users.user_id = user_roles.user_id
INNER JOIN roles ON user_roles.role_id = roles.role_id;

  8. Nested INNER JOIN in MySQL:

    • Description: Nest INNER JOIN operations to join multiple tables in a more complex query.
    • Example:
      SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id
WHERE orders.order_date >= '2023-01-01';
Copyright © devanddep.com | All Rights Reserved