The difference between where and on in MySQL and when to use them

When I was writing join table queries before, I always couldn't tell the difference between where and on, which sometimes caused some small problems in the SQL I wrote. Here is a special article to record it. If you can't tell the difference, please refer to

What is the difference between the two and when to use them

Note: Distinguish between on and where. First, we divide the connection into inner connection and non-inner connection. In the case of inner connection, the functions of on and where are the same. Usually we cannot distinguish the difference between them and we are talking about non-inner connection.

Generally, on is used to connect two tables. It is only the condition of the connection. In the internal connection, on can be omitted. At this time, it represents the Cartesian product of the two tables. After using on connection, MySQL will generate a temporary table, and where is based on the temporary table, according to the where clause to filter out the records that meet the conditions, so where is used to filter

Inner join

Note: join defaults to inner join. When it is an inner join, you can think of on and where as having the same effect.

Non-inner join (left join, right join, full join, etc.)

Generally, the difference is not clear when using non-internal connections.

Example

Next, we create two tables (insert 4 data records into each table, and the two tables are linked by trade_id) to illustrate their differences. The SQL script is attached at the bottom of this article. Then we use a join table query to illustrate the difference between on and where.

1. Inner join connects two tables (without on and where)

select * from hopegaming_main.test_1234 join hopegaming_main.test_1235

Equivalent to

select * from hopegaming_main.test_1234,hopegaming_main.test_1235

The result set is the Cartesian product of the two tables

2. Inner join connects two tables (with on)

select * from hopegaming_main.test_1234 t1 join hopegaming_main.test_1235 t2 on t1.trade_id = t2.trade_id 

The result set is data with the same trade_id in two tables

3. Inner join connects two tables (with where)

select * from hopegaming_main.test_1234 t1 join hopegaming_main.test_1235 t2 where t1.trade_id = t2.trade_id 

The result set is the data of the same trade_id in two tables

From the results of 2 and 3, we can see that when using inner join, on and where have the same effect.

4. Left join (the following example uses left join to connect two tables)

select * from hopegaming_main.test_1234 t1 left join hopegaming_main.test_1235 t2 on t1.trade_id = t2.trade_id  

The result set is based on the table on the left. It directly searches for equal values ​​on the right based on trade_id and then joins. If there is no matching data in the right table, it will be displayed as null.

5. Left join (the following example uses left join to connect two tables) Connect two tables, and there is a constant equation in the connection condition

select * from hopegaming_main.test_1234 t1 left join hopegaming_main.test_1235 t2 on t1.trade_id = t2.trade_id and t2.nick_name = 'wangwu'

The result set is based on the table on the left. If the on join condition does not find a matching record, null is displayed.

6. Left join (the following example uses left join to connect two tables) Connect two tables and put the constant expression into the where clause

select * from hopegaming_main.test_1234 t1 left join hopegaming_main.test_1235 t2 on t1.trade_id = t2.trade_id where t2.nick_name = 'wangwu'

The result will only show the data that meets the where clause. If there is no match, it will not be shown because it is the data in the temporary table after filtering the connection.
It is just a connection. If there is no matching data on the right, null will be displayed, and the data on the left will be displayed without being filtered. This is the biggest difference between where and on.

Scripts for creating tables and inserting data:

CREATE TABLE `hopegaming_main`.`test_1234` (
  `id` varchar(30) NOT NULL COMMENT 'ID number',
  `name` varchar(100) DEFAULT NULL COMMENT 'Name',
  `trade_id` varchar(100) DEFAULT NULL COMMENT 'Transaction id',
  `gender` tinyint(4) DEFAULT NULL COMMENT 'Gender',
  `birthday` timestamp(6) NOT NULL COMMENT 'Birthdate',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_trade_id` (`trade_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

INSERT INTO hopegaming_main.test_1234
(id, name, trade_id, gender, birthday)
VALUES('1', 'zhangsan', '123', 0, CURRENT_TIMESTAMP(6)),
('2', 'zhaosi', '124', 0, CURRENT_TIMESTAMP(6)),
('3', 'wangwu', '125', 0, CURRENT_TIMESTAMP(6)),
('4', 'maqi', '126', 0, CURRENT_TIMESTAMP(6));


CREATE TABLE `hopegaming_main`.`test_1235` (
  `id` varchar(30) NOT NULL COMMENT 'ID number',
  `nick_name` varchar(100) DEFAULT NULL COMMENT 'Alias',
  `trade_id` varchar(100) DEFAULT NULL COMMENT 'Transaction id',
  `address` varchar(100) DEFAULT NULL COMMENT 'Address',
  `email` varchar(6) NOT NULL COMMENT 'Date of Birth',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_trade_id` (`trade_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

INSERT INTO hopegaming_main.test_1235
(id, nick_name, trade_id, address, email)
VALUES('1', 'zhangsan', '123', 'beijing', '0000'),
('2', 'wangwu', '123', 'tianjin', '1111'),
('3', 'maqi', '124', 'shanghai', '2222'),
('4', 'yangliu', '127', 'shanxi', '3333');

Summarize

This is the end of this article about the difference between where and on in MySQL and when to use them. For more information about the difference between where and on in MySQL, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!