How to realize the query of connected tables in mysql Database

This article will explain in detail how mysql database to achieve even table query, Xiaobian think quite practical, so share to you as a reference, I hope you can read this article after harvest.

1. First introduce the table join classification (inner join, outer join, cross join) and join method (as follows):

A) inner join: join, inner join

B) External links: left join, right join, right outer join, union

C) Cross join: cross join

2. inner connection

Find the data with the same ID in two tables, the query results will be combined into a table, and the output ids in the two tables are the same.

select a.*, b.* from tableA a, tableB b where a.id=b.userid

Or use the following statement

select a.*, b.* from tableA a inner join tableB b on a.id=b.userid

3. any such linked

External links include left link and right link left join ,right join; take the keyword left(right) as the reference object, and use its left (right) as the main table. At this time, all the data in the main table must be output while satisfying the query conditions after on (even if the data does not correspond to the data in the slave table).

www.example.com http://m.zykdtj.com/

select a.*, b.* from tableA a left join tableB b on a.id=b.userid

right outer link

select a.*, b.* from tableA a right join table B on a.id=b.userid

Total external connection:

This outer join is not supported in MySQL databases: its role is not only to satisfy the conditions behind on, but also to output unmatched data in two tables.

3. cross-linked

select a.*, b.* from tableA a cross join tableB b

Each row in the left table (the table to the left of the "cross join" keyword) is combined with all rows in the right table (the table to the right of the "cross join" keyword), and the result of the cross join is a Cartesian product. At this time, the types of data matching are too diverse. Generally speaking, the effect of this method is very small for some needs to find database information through corresponding IDs.

About "mysql database how to achieve even table query" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.