- let's dive into INNER JOINs in MySQL with an example.
- Consider two hypothetical tables: `orders` and `customers`. The `orders` table contains information about orders made by customers, and the `customers` table contains information about the customers themselves.
Here's what the tables schema might look like:
- customers table:
-- Create the 'customers' table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50)
);
-- Insert data into the 'customers' table
INSERT INTO customers (customer_id, customer_name)
VALUES
(101, 'Alice'),
(102, 'Bob'),
(103, 'Carol'),
(104, 'David');
- orders table:
-- Create the 'orders' table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Insert data into the 'orders' table
INSERT INTO orders (order_id, customer_id, order_date)
VALUES
(1, 101, '2023-08-01'),
(2, 102, '2023-08-02'),
(3, 103, '2023-08-02'),
(4, 101, '2023-08-03');
- Now, let's say you want to retrieve a list of orders along with the customer names who made those orders. This is where the INNER JOIN comes into play:
SELECT orders.order_id, orders.order_date, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
In this query:
- orders and customers are the two tables you want to join.
- orders.customer_id is the column from the orders table that is related to the customers.customer_id column.
- The ON clause specifies the condition for the join. Here, it's matching rows where the customer_id values are equal in both tables.
The result of this query would be:
- The INNER JOIN only includes rows where there's a match in both the `orders` and `customers` tables based on the specified condition. In this example, only the orders made by customers with corresponding names are included in the result.
No comments:
Post a Comment