Mysql tutorial detailed description of left join SQL left join statements
Mysql left join query is a method of joint query. That is to say, two related tables are joined together for query in this way, which makes it easier to call data and avoids multi-layer loop nesting.
Left join keyword used for left join query
, The written explanation is as follows:
Left Outer Join (left join): the result set includes both matching rows of the connected table and all rows of the left connected table.
Next I will explain that the results returned by the left link query contain all the data in the left table of the left join keyword, it also contains qualified resources in the right table that matches the left table. Simply put, the table is centered on the left table and associated with qualified resources in the right table.
SQL statement Syntax:
SQL: select a. a, a. B, a. c, B. c, B. d, B. f from a left out join B on a. a = B. c
I believe you will be dizzy after reading this article. It doesn't matter. I will explain it to you and you will understand it. It seems complicated and actually well organized. It means:
I
Select a. a, a. B
Medium
A. a is the meaning of field a in table.
II
Select and from are the fields to be queried in table B of Table.
3.
From a left out join B
That is, from table a to table B on the left link,
The matching condition is that the field of Table a is equal to the c field of Table B.
I believe that everyone understands 80% through these explanations. Let's explain it to you through an example:
Create two tables first
Table pic
Table used to store image names
Create table 'image'. 'pic '(
'Id' int (10) not null auto_increment primary key,
'Pname' varchar (20) character set utf8 collate utf8_unicode_ci not null
) Engine = innodb
Table 2 Stores image comments
Create table 'image'. 'comment '(
'Id' int (10) not null auto_increment primary key,
'Pid 'int (10) not null,
'Content' varchar (20) character set utf8 collate utf8_unicode_ci not null
) Engine = innodb
In the following example, I want to query the two tables in combination using one SQL statement. Note that we used to query the two tables first by writing the SQL statement of one table and then querying the other table cyclically based on the conditions, in this query, the returned results are placed in an array,
SQL statement Syntax: select
Pic. *, comment .*
From
Pic left join comment
On
Pic. id = comment. pid
The Code is as follows:
$ Conn = mysql_connect ("localhost", "root ","");
If (! $ Conn ){
Die ('failed to connect to mysql database Tutorial: '. mysql_error ());
}
Mysql_select_db ('image ');
$ SQL = "select
Pic. *, comment .*
From
Pic left join comment
On
Pic. id = comment. pid ";
$ Re = mysql_query ($ SQL );
While ($ re1 = mysql_fetch_array ($ re ))
{
$ Arr [] = $ re1;
}
Print_r ($ arr );
The output is as follows:
Array
(
[0] => array
(
[0] => 1
[Id] => 1
[1] => aaaaaaaaa
[Pname] => aaaaaaaaa
[2] => 1
[3] => 1
[Pid] => 1
[4] => ccccccc
[Content] => ccccccc
)
[1] => array
(
[0] => 2
[Id] => 2
[1] => bbbbbbbbbbbbb
[Pname] => bbbbbbbbbbbbb
[2] => 2
[3] => 2
[Pid] => 2
[4] => vvvvvvv
[Content] => vvvvvvv
)
)
OK