MSQL Series (14) Mysql Practice – The difference between SQL statement left join inner join On and Where statement

Mysql practice-the difference between SQL statement On and Where statement

Earlier we explained the selection principle of the underlying driver table of Join, and also learned about the two basic SQL query table connection methods: inner join and outer join.
But what is the difference between on and where statements when we query multiple tables?

where is the filtering condition. If it does not meet where, it will not appear in the results.
on is the connection condition. For inner joins, on and where have the same effect.
For outer joins, if the filter condition of on cannot be matched in the driven table, the record will be added to the result set.
The data of the driven table that does not meet the matching conditions are all filled with NULL values.
First on, then left join, then where
When using the on keyword, it will be filtered based on the condition after on. If the condition is true, the row will be returned.
The priority of on is higher than that of left join, so the left join keyword will return all unmatched rows in the left table, and then generate a temporary table to return
Where rows are filtered after left join, that is, the temporary table is filtered after the temporary table is generated.

Let’s practice SQL in practice.

Article directory

- - Mysql practice-the difference between SQL statement On and Where statement
  - - 1. Create tables and test data
    - 2. The on connection filter condition of the inner join is equivalent to the where filter condition
    - 3.left join outer join on connection condition
    - 4.left join where filter conditions
    - 5. More complex comparison between on and where

1. Create tables and test data

We first create two tables test_user and test_order as our test tables and test data.

test_user 5 pieces of data, the index only has the primary key id
test_order 3 pieces of data, the index also only has the primary key id

#Create test_user
CREATE TABLE `test_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'user name',
  `age` int DEFAULT NULL COMMENT 'age',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`),
  KEY `idx_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User table';

#Create table test_order
CREATE TABLE `test_order` (
  `id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL COMMENT 'User id is the only primary key id of test_user',
  `order_name` varchar(32) NOT NULL DEFAULT 'Order information',
  `pay` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Order Table';

Insert data

#Insert user user data
INSERT INTO `prepare`.`test_user` (`id`, `user_name`, `age`) VALUES (1, 'aa', 10);
INSERT INTO `prepare`.`test_user` (`id`, `user_name`, `age`) VALUES (2, 'bb', 20);
INSERT INTO `prepare`.`test_user` (`id`, `user_name`, `age`) VALUES (3, 'cc', 30);
INSERT INTO `prepare`.`test_user` (`id`, `user_name`, `age`) VALUES (4, 'dd', 40);

#Insert order order data
INSERT INTO `prepare`.`test_order` (`id`, `user_id`, `order_name`, `pay`) VALUES (1, 1,'clothes', 100);
INSERT INTO `prepare`.`test_order` (`id`, `user_id`, `order_name`, `pay`) VALUES (2, 2,'shoes', 200);
INSERT INTO `prepare`.`test_order` (`id`, `user_id`, `order_name`, `pay`) VALUES (3, 2,'TV', 300);

According to the table records, we can know

The user table has 4 users, aa, bb, cc, dd
The order order table has 1, 2, and 3 orders, aa is a piece of clothing, bb user is a pair of shoes, and a TV

2. The on connection filter condition of the inner connection is equivalent to the where filter condition

When the connection condition is inner join, the filter condition of on connection is equivalent to the where filter condition.

That is to say, if you put the filter conditions after the on statement or after the where statement, the effect will be the same.

#on statement filter conditions
select * from test_user inner join test_order on test_user.id = test_order.user_id;
#where statement as filter condition
select * from test_user inner join test_order where test_user.id = test_order.user_id;

3.left join outer join on connection condition

When performing a left join outer connection, there is a big difference between on connection condition filtering and where condition filtering. The on condition is the driven table and unmatched ones must also be displayed and filled with NULL.

But the where statement filters out everything that is not satisfied. Let’s actually see the effect.

The filter conditions of the on statement are displayed and filled with NULL if they do not meet the requirements.

#Find the driver table
explain select * from test_user left join test_order on ( test_user.id = test_order.user_id) and test_order.user_id = 2;
#search result
select * from test_user left join test_order on ( test_user.id = test_order.user_id) and test_order.user_id = 2;

View Results

test_user is the driving table, then test_order is the driven table
The on condition is ( test_user.id = test_order.user_id) and test_order.user_id = 2
Is only the data with test_order.user_id = 2 returned? No, all data with user_id not equal to 2 are also returned.
However, the data in their driven table data order are all filled with NULL.
Therefore, the filtering conditions after on are not for filtering, but for matching. Unmatched ones are filled with NULL.

4.left join where filter conditions

Earlier we saw using on to connect two tables, and set test_order.user_id = 2
But the returned result is not the data with user_id = 2, but the unmatched data is replaced with NULL.

What if it is a where statement?
What is the effect if test_order.user_id = 2 is hung behind the where statement?

#View driver table
explain
select * from test_user left join test_order on ( test_user.id = test_order.user_id) where test_order.user_id = 2;

#Execute query statement
select * from test_user left join test_order on ( test_user.id = test_order.user_id) where test_order.user_id = 2;

View execution results

test_user is the driving table, then test_order is the driven table
The on condition is (test_user.id = test_order.user_id)
where condition is where test_order.user_id = 2
As a data result, only the data with test_order.user_id = 2 will be returned, and nothing else will be returned.
So the filtering conditions after where are used for filtering. As long as where is not satisfied, the result will not be satisfied.

5. More complex comparison between on and where

If you still can’t tell the difference between on and where in the above example, let’s use another one that is more intuitive and you can see the difference at a glance.

#on conditions
select * from test_user left join test_order on test_user.id = test_order.user_id and test_order.pay > 100;
#where condition
select * from test_user left join test_order on test_user.id = test_order.user_id where test_order.pay > 100;

Let’s take a look at the execution results

on conditional query
- Pay > 100 data returned
- There is also one for pay <= 100, but it is filled with NULL.
- Returned all the data matching the data of the driven table test_user connected to the driven table test_order. 5 pieces of data, and 2 pieces of data >100 are displayed normally.
- The data of pay <= 100 is filled with NULL
- on is executed first, and the connection condition generates a temporary table, so the data is there, 5 pieces of data
- Then match the 2 items with pay >100 and display them, and fill the rest with NULL.
where condition query
- As a result, there are only 2 pieces of data. If pay>100, there are only 2 pieces of data.
- where is filtered based on the temporary table
- Those that are not satisfied will not be presented to the return result.

At this point, we have thoroughly distinguished the difference between the on statement and the where statement, which is very important for us to handle the business correctly.