Mysql inner join on usage examples (must read)

Grammatical rules

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

First create two tables, 1. User, 2. User category

User Table

CREATE TABLE `user` (
 `id` int(32) NOT NULL AUTO_INCREMENT,
 `name` varchar(16) NOT NULL,
 `kindid` int(32) NOT NULL,
 PRIMARY KEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

User Category Table

CREATE TABLE `userkind` (
 `id` int(32) NOT NULL AUTO_INCREMENT,
 `kindname` varchar(16) NOT NULL,
 PRIMARY KEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

Insert some data into the user table

INSERT INTO `user` VALUES (1,'Xiao Ming',1),(2,'Xiao Hong',1),(3,'Han Han',2); Insert some data into the userkind table

INSERT INTO `userkind` VALUES (1,'Ordinary member'),(2,'VIP member');

As shown in the figure:

The following is an example of a console query:

Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use join;
Database changed
mysql> select * from `user`;
+----+------+--------+
| id | name | kindid |
+----+------+--------+
| 1 | Xiao Ming | 1 |
| 2 | Xiaohong | 1 |
| 3 | Hanhan | 2 |
+----+------+--------+
3 rows in set (0.00 sec)

mysql> select * from `userkind`;
+----+----------+
| id | kindname |
+----+----------+
| 1 | Ordinary Member|
| 2 | VIP Membership |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from `user` inner join `userkind` on user.kindid=userkind.id;
+----+------+--------+----+----------+
| id | name | kindid | id | kindname |
+----+------+--------+----+----------+
| 1 | Xiao Ming | 1 | 1 | Ordinary Member |
| 2 | Xiaohong | 1 | 1 | Ordinary member |
| 3 | Hanhan | 2 | 2 | VIP Member |
+----+------+--------+----+----------+
3 rows in set (0.02 sec)

mysql> select `id` as `user ID`,`name` as `user name`,`kindname` as `user category` from
`user` inner join `userkind` where user.kindid=userkind.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
mysql> select `user`.`id` as `user ID`,`name` as `user name`,`kindname` as `user category` from
  -> `user` inner join `userkind` where `user`.`kindid`=`userkind`.`id`;
+--------+--------+----------+
| User ID | User Name | User Category |
+--------+--------+----------+
| 1 | Xiao Ming | Ordinary Member |
| 2 | Xiaohong | Ordinary Member |
| 3 | Hanhan | VIP Member |
+--------+--------+----------+
3 rows in set (0.00 sec)

mysql> select `user`.`id` as `userID`,`name` as `username`,`kindname` as `userkind` from `user` inner join `userkind` on `user`.`kindid`=`userkind`.`id`;
+--------+--------+----------+
| User ID | User Name | User Category |
+--------+--------+----------+
| 1 | Xiao Ming | Ordinary Member |
| 2 | Xiaohong | Ordinary Member |
| 3 | Hanhan | VIP Member |
+--------+--------+----------+
3 rows in set (0.00 sec)

mysql>

It should be noted that on here is basically equivalent to where (I feel)

When a column exists in both tables but cannot be distinguished, you need to use `table name`.`field name` to distinguish them.

as is an alias. Just look at the example above!

The above usage example of Mysql inner join on (must read) is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in: