MySQL Join table connection

1. Table connection, that is, the merging of two tables, the records of the merged table through the intermediate fields, each matching the records of the table on the left, form a temporary merged table, and each record value is two tables one by one exactly corresponding instance

Try the following examples:

[Email protected]# mysql-u root-p password;EnterPassword:*******Mysql> UseRunoob;DatabaseChangedmysql>SELECT*From Tcount_tbl;+-----------------+----------------+|Runoob_author|Runoob_count|+-----------------+----------------+|Mahran| 20 ||Mahnaz|Null|| Jen |Null|| Gill | 20 || John Poul | 1 || Sanjay | 1 |+-----------------+----------------+6RowsInch Set (0.01Sec)Mysql>SELECT* FromRunoob_tbl;+-------------+----------------+-----------------+-----------------+|runoob_id|Runoob_title|Runoob_author|Submission_date|+-------------+----------------+-----------------+-----------------+| 1 | LearnPhp| John Poul | 2007-05-24 || 2 | Learn Mysql | AbdulS| 2007-05-24 || 3 |tutorial |sanjay |2007-05-06  |+-------------+----------------+-----------------+-----------------+3 rows in set   (0.00 Secmysql>            

Next we'll use MySQL's INNER join (or omit INNER using join, same effect) to join the above two tables to read all Runoob_author fields in the Runoob_tbl table in Tcount_ Runoob_count field value corresponding to the TBL table:

Mysql>SELECT A.runoob_id,A.Runoob_author,B.Runoob_count from Runoob_tbl a INNER joins TCOUNT_TBL B on a.Runoob_author=B.Runoob_author;+-----------+---------------+--------------+|runoob_id|Runoob_author|Runoob_count|+-----------+---------------+--------------+| 1 | John poul |1 ||  3 |sanjay |1 |+-----------+---------------+--------------+2in set  (0.00 Sec) 
 
         

The above SQL statements are equivalent to:

Mysql>SELECT A.runoob_id,A.Runoob_author,B.Runoob_count from Runoob_tbl A,Tcount_tbl b WHERE A.Runoob_author=B.Runoob_author;+-------------+-----------------+----------------+|runoob_id|Runoob_author|Runoob_count|+-------------+-----------------+----------------+| 1 | John Poul | 1 ||  3 |  Sanjay |  1 |  +-------------+-----------------+----------------+2 rows in set (0.01  SEC)mysql>             

MySQL left JOIN

MySQL left join differs from join. The MySQL left JOIN reads all the data from the data table on the right, even if there is no corresponding data on the table.

Instance

Try the following example to Runoob_tbl to the left table,tcount_tbl to the right table, and to understand the application of MySQL Ieft join:

[Email protected]# mysql-u root-p password;EnterPassword:*******Mysql> UseRunoob;DatabaseChangedmysql>SELECT A.runoob_id,A.Runoob_author,B.Runoob_count from Runoob_tbl a left JOIN tcount_tbl B on a.Runoob_author=B.Runoob_author;+-------------+-----------------+----------------+|runoob_id|Runoob_author|Runoob_count|+-------------+-----------------+----------------+| 1 | John Poul | 1 || 2 |abdul S || |  3 |sanjay |1 |+-------------+-----------------+----------------+3 rows in set (0.02 Sec)       

 

MySQL Join table connection