Use of on and where in SQL and their differences

In writing SQL scripts, a multi-table join query operation needs to use the on and where conditions, but often confuses the use of both, resulting in a fetch error. To do this, use the test data to summarize the following:

1, existing two test tables, table_a and Table_b. table_a Table data: 14 Records, primary key is (USER_ID,BRAND_ID), is the user in each month access to the Internet brand and its frequency of access. Table_b the data in the table: The primary key is user_id, is the user Base category table, and type_id is the user category for that month.  (1) The effect of connecting by user_id and month_id:

1 SELECTA1.user_id2 , A1. month_id3 , A1. brand_id4 , A1. Rate5, A2.user_id6 , A2. month_id7 , A2. type_id8  fromtable_a A19  Left JOINTable_b A2Ten  onA1.user_id=A2.user_id One  andA1. month_id=A2. month_id A;

The final number of records equals the number of records in the primary table.

The selection of the primary table is important, where the number of records is equal to the number of primary table records, but not always, and is related to the composition of the records in the non-primary table. (2) Add the effect of the Where Condition:

1 SELECTA1.user_id2 , A1. month_id3 , A1. brand_id4 , A1. Rate5, A2.user_id6 , A2. month_id7 , A2. type_id8  fromtable_a A19  Left JOINTable_b A2Ten  onA1.user_id=A2.user_id One  andA1. month_id=A2. month_id A WHEREA1. brand_id='501b03' -;

describes where conditions are a re-filtering of query results resulting from connection completion. Will affect the last number of records. (3) Continue to add the effect of the Where Condition:

1 SELECTA1.user_id2 , A1. month_id3 , A1. brand_id4 , A1. Rate5, A2.user_id6 , A2. month_id7 , A2. type_id8  fromtable_a A19  Left JOINTable_b A2Ten  onA1.user_id=A2.user_id One  andA1. month_id=A2. month_id A WHEREA1. brand_id='501b03' -  andA2. type_id=Ten -;

It is further explained that the where condition is a two filter of the query result set produced by the connection.

(4) The effect of adding more on join conditions:

1 SELECTA1.user_id2 , A1. month_id3 , A1. brand_id4 , A1. Rate5, A2.user_id6 , A2. month_id7 , A2. type_id8  fromtable_a A19  Left JOINTable_b A2Ten  onA1.user_id=A2.user_id One  andA1. month_id=A2. month_id A  andA1. brand_id='501b03' -;

This is summed up as follows: The on condition is added to the primary table, which filters the records of the primary table participating in a multi-table connection, and only the primary table records that meet this condition participate in the join operation with other tables, getting the fields in the other table to form a required record Records that do not conform to this on condition in the main table will also go into the final result table, but will not participate in the connection to other tables, so the fields obtained in the other tables in the record are all null. This shows the particularity of the main table, in the left join, the main table is the leftmost table of the multi-table connection, all the records in the table will appear in the final result set, but only the records that satisfy the on condition will participate in the connection operation with other tables.

1 SELECTA1.user_id2 , A1. month_id3 , A1. brand_id4 , A1. Rate5, A2.user_id6 , A2. month_id7 , A2. type_id8  fromtable_a A19  Left JOINTable_b A2Ten  onA1.user_id=A2.user_id One  andA1. month_id=A2. month_id A  andA2. type_id=Ten -;

It is summarized as follows: The on condition of the connected table is added here, and this condition does not affect the record of the main table in the final result set, it is mainly to filter the connected table first, it will let the records in the connected table meet this condition participate in the connection operation with the main table. Other records that do not meet the on condition simply do not have the opportunity to participate in the connection to the primary table.

1 SELECTA1.user_id2 , A1. month_id3 , A1. brand_id4 , A1. Rate5, A2.user_id6 , A2. month_id7 , A2. type_id8  fromtable_a A19  Left JOINTable_b A2Ten  onA1.user_id=A2.user_id One  andA1. month_id=A2. month_id A  andA1. brand_id='501b03' -  andA2. type_id=Ten -;

This table combines the above two aspects, using brand_id= ' 501b03 ' to filter the main table, so that records matching this condition participate in the connection with other tables, other records do not participate in the connection, but still enter the result table (empty field null); use type_id= 10 pairs of connected tables A2 first make a screening to allow records that meet this condition to participate in the connection to the main table A1, records that do not meet this condition are excluded from consideration, and there is no opportunity to participate in the connection and access to the results table. (5) Change the primary key of the Table_b, set it to (USER_ID,MONTH_ID), and add a record as follows:in this way, there are two records corresponding to user_id=989832 in Table_b. execute the following sql:

1 SELECTA1.user_id2 , A1. month_id3 , A1. brand_id4 , A1. Rate5, A2.user_id6 , A2. month_id7 , A2. type_id8  fromtable_a A19  Left JOINTable_b A2Ten  onA1.user_id=A2.user_id One;

As you can see, the number of records in the final query result set is no longer 14 records, but 15, which shows that the number of records in the final result set is not the same as the number of records in the primary table, but is determined by the main table and the joined table based on the join conditions, and it can be explained that regardless of the on condition, Much simpler and more complex, as long as there is no where condition, the number of records in the final query result set will not be less than the number of primary table records. For this example, the 14 records in the primary table are entered into the result table, because the connection conditions in the primary table are only user_id equal, so for the A2 table, user_id=989832 has two records, so it will be connected to the record user_id=989832 the A1 table. As a result, two connection results are recorded, so the final result set is increased to 15 records.

1 SELECTA1.user_id2 , A1. month_id3 , A1. brand_id4 , A1. Rate5, A2.user_id6 , A2. month_id7 , A2. type_id8  fromtable_a A19  Left JOINTable_b A2Ten  onA1.user_id=A2.user_id One  andA1. month_id=A2. month_id A;

The connection conditions above for the A1 and A2 tables add a month_id equal condition so that only A2 table records that meet this condition will participate in the connection, thus eliminating a record of A2 in the user_id=989832 table. So the last connection only produced 14 records.

  2, in summary, summarized as follows:(1) on condition, whether it is a1.col1=a2. COL2 or A2.COL3=XX, is the A2 table (connected table) to carry out the conditions of filtering, will meet the criteria of the record taken out, participate in the connection with the main table operation, inconsistent with the conditions of the record will be considered to participate in the connection. For conditional a1.col4=xxx, the function is to filter the A1 table (the primary table), and records that match that condition will participate in the connection to other tables, and records that do not conform to the conditions will enter the final result table, and null for empty. andA1. Col1=a2. COL2 is also a filter on the A1 table, so that the records in the A1 table that meet the conditions participate in the connection to other tables, and the non-satisfied void complement NULL continues to enter the result table. No matter how the on condition is used, as long as there is no where condition filter, the number of records for the result set of the last query is greater than or equal to the original record number of the primary table.(2) Where condition is the filter for the result set after the query is complete. The filter criteria field that you can use is arbitrary, because a multi-table query is essentially a full-field join query between multiple tables, except that you can specify the last number of fields to display in a record. The Where condition fundamentally affects the number of records for the last query result set. (3) If the filtering of the aggregation function results after the join query is done using the group by +having.  

Use of on and where in SQL and their differences