INNER JOIN is a type of join in MySQL that combines rows from two or more tables based on a matching condition specified in the JOIN clause.
The resulting table includes only the rows that have matching values in both tables.
The syntax of an INNER JOIN statement in MySQL is as follows:
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
Here, table1
and table2
are the names of the tables you want to join, and column
is the column name that you want to match in both tables.
For example, suppose you have two tables, customers
and orders
, and you want to retrieve the names of all customers who have placed orders. You can use INNER JOIN as follows:
SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
In this example, customers
and orders
are the names of the two tables, and customer_id
is the column that links the two tables.
The resulting table will include only the rows where a matching customer_id exists in both tables, and it will show the name of the customer and the order date.
Table Preparations
mysql> INSERT INTO Stores
-> VALUES (NULL, 'London', 'London_1', 'Gold PSU', 153, 100);
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO Stores
-> VALUES (NULL, 'London', 'London_2', 'Gold PSU', 75, 100);
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO Stores
-> VALUES (NULL, 'Berlin', 'Berlin_1', 'Green PSU', 50, 120);
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO Stores
-> VALUES (NULL, 'Berlin', 'Berlin_2', 'XYZ Motherboard', 5, 75);
Query OK, 1 row affected (0.20 sec)
mysql> INSERT INTO Stores
-> VALUES (NULL, 'Moscow', 'Moscow_1', 'Extension Cable', 50, 25);
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO Stores
-> VALUES (NULL, 'Moscow', 'Moscow_2', 'LPT Cables', 500, 10);
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO Stores
-> VALUES (NULL, 'Miami', 'Miami_1', 'COM Cables', 1450, 5);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO Stores
-> VALUES (NULL, 'Paris', 'Paris_1', 'NIC', 350, 15);
Query OK, 1 row affected (0.10 sec)
mysql>
The purpose of this data is to provide examples for using JOINs here and in future tutorials.
What is in Stores:
mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city | store_name | product | available | price |
+------+--------+------------+-----------------+-----------+-------+
| 1 | London | London_1 | Gold PSU | 153 | 100 |
| 2 | London | London_2 | Gold PSU | 75 | 100 |
| 3 | Berlin | Berlin_1 | Green PSU | 50 | 120 |
| 4 | Berlin | Berlin_2 | XYZ Motherboard | 5 | 75 |
| 5 | Moscow | Moscow_1 | Extension Cable | 50 | 25 |
| 6 | Moscow | Moscow_2 | LPT Cables | 500 | 10 |
| 7 | Miami | Miami_1 | COM Cables | 1450 | 5 |
| 8 | Paris | Paris_1 | NIC | 350 | 15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)
mysql>
New data in Documents table:
mysql> INSERT INTO Documents
-> VALUES (NULL, 1235547, 1);
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO Documents
-> VALUES (NULL, 2223371, 3);
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO Documents
-> VALUES (NULL, 6687451, 4);
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO Documents
-> VALUES (NULL, 6548529, 2);
Query OK, 1 row affected (0.14 sec)
mysql>
What is in Documents now:
mysql> SELECT * FROm Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
| 1 | 1235547 | 1 |
| 2 | 2223371 | 3 |
| 3 | 6687451 | 4 |
| 4 | 6548529 | 2 |
+------+-----------------+------+
4 rows in set (0.00 sec)
mysql>
INNER JOIN Example
mysql> SELECT stores.city, stores.store_name, documents.internal_number
-> from stores
-> INNER JOIN documents
-> ON stores.s_id = documents.s_id
-> ORDER BY stores.city;
+--------+------------+-----------------+
| city | store_name | internal_number |
+--------+------------+-----------------+
| Berlin | Berlin_1 | 2223371 |
| Berlin | Berlin_2 | 6687451 |
| London | London_1 | 1235547 |
| London | London_2 | 6548529 |
+--------+------------+-----------------+
4 rows in set (0.00 sec)
mysql>
The JOIN is performed by matching the "s_id" column in the "documents" table to the "s_id" column in the "stores" table.
The result set includes only those rows for which there is a match in both tables.
What these lines mean ?
-> from stores
-> INNER JOIN documents
-> ON stores.s_id = documents.s_id
-> ORDER BY stores.city;
These lines of code are a SQL query that performs an inner join between two tables, 'stores' and 'documents', based on a common column 's_id'.
The first line, "FROM stores", specifies the primary table being queried, in this case 'stores'.
The second line, "INNER JOIN documents", specifies the secondary table being joined with the primary table, in this case 'documents'.
The third line, "ON stores.s_id = documents.s_id", specifies the join condition that the two tables are being joined on the common column 's_id'.
The fourth line, "ORDER BY stores.city", specifies the ordering of the results based on the 'city' column of the 'stores' table.
This query will retrieve all the rows from the two tables where the 's_id' value matches in both tables, and then return the 'city', 'store_name' and 'internal_number' columns of these matched rows, ordered by 'city'.
No comments:
Post a Comment