MYSQL join on and where

Original: http://www.cnblogs.com/Jessy/p/3525419.html

Left join: Connects all the records in the left table with the same records as the join fields in the right table.

Right join: Connects all the records in the right table with the same records as the join fields in the left table.

INNER JOIN: An inner join, also known as an equivalent connection, returns only rows in which the joined fields are equal in two tables.

Full join: Outer JOIN, returns rows from two tables: Left JOIN + RIGHT join.

Cross join: The result is a Cartesian product, that is, the number of rows in the first table multiplied by the number of rows in the second table.

Keyword: on

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

When using left Jion, the difference between on and where conditions is as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true.

2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

Suppose there are two tables:

Table 1:TAB2

Id	Size
1	10
2	20
3	30

Table 2:TAB2

Size	Name
10	Aaa
20	Bbb
20	Ccc

Two sql:
1. Select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size) where tab2.name= ' AAA '
2. Select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size and Tab2.name= ' AAA ')

The first SQL procedure: 1. Intermediate table On condition: Tab1.size = Tab2.size /table> tab1.size tab2.name 10 10 2 20 20 bbb 20 20 3 30 2, then the intermediate table filter Where Condition: Tab2.name= ' AAA ' Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa

The second SQL procedure: 1, intermediate table on condition: Tab1.size = tab2.size and tab2.name= ' AAA ' (the condition is not true also returns records from the left table) Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa 2 20 (NULL) (NULL) 3 30 (NULL) (NULL)

In fact, the key reason for the above results is the particularity of the left Join,right Join,full join, regardless of whether the on condition is true will return the records in the left or right table, and full has a set of attributes of left and right. and inner jion does not have this particularity, the condition is placed in and where, the result set returned is the same.

SQL join is used to query data from these tables based on the relationship between the columns in two or more tables. Common join operations are: Inner joins, outer joins, Cross joins, and so on. If we want to get data in two or more tables that match rows from one table to rows in another table, then we should consider using join because the JOIN operation has attributes that join the table or function for querying. A variety of SQL implementations, both Oracle and MS SQL Server, have demonstrated that their execution order is first on, after where, a statement with an out-of-band Connection:

SELECT * FROM A1 a LEFT join A2 B on a.aa=b.cc where b.cc= ' 3 '
, the outer join is performed first, and then the where condition is used for filtering. However, after all, a large amount of data connection will take a long time, in the case of semantics allow, if you want to filter a table first, then connect, how should you do? Can be written in the following form:

SELECT * FROM A1 a LEFT join (SELECT * from A2 where a2.cc= ' 3 ') b on a.aa=b.cc;
Of course, the result of where or first on is often different. This is just a way to point out how to force the filter to occur first.

MYSQL join on and where