Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve external connections in mysql. The quality of the article is high. Therefore, Xiaobian shares it with you as a reference. I hope you will have a certain understanding of relevant knowledge after reading this article.

First my sql does not support oracle's (+)

Inner join: join only matching rows

Left outer join: Contains all rows of the table on the left (regardless of whether there are any matching rows in the table on the right), and all matching rows in the table on the right

Right outer join: Contains all rows of the table on the right (regardless of whether there are any matching rows in the table on the left), and all matching rows in the table on the left

Total outer join: Contains all rows of the left and right tables, regardless of whether there are rows matching them in the other table.

Cross-join generates Cartesian product-it does not use any matching or selection conditions, but directly connects each row in one data source with each row in another data source.

All rows match one by one.

Let me give you an example.

table A

id name

1 bunk

2 Lee

3 and the King

table B

id address A_id

1 Beijing 1

2 Shanghai 3

3 Nanjing 10

Inclusivity: Table A contains Table B, and the left link is complete. (left join or left outer join )

SQL statements are as follows:

SELECT A.name, B.address

FROM A

LEFT JOIN B ON A.id = B.A_id

The query results are:

name address

from Beijing

Li NULL

Wang Shanghai

Inclusivity: Table B contains Table A, right link right table is complete. (right join or right outer join )

SQL statements are as follows:

SELECT A.name, B.address

FROM A

RIGHT JOIN B ON A.id = B.A_id

The query results are:

name address

from Beijing

Wang Shanghai

Nanjing NULL

Exclusivity:A row is returned only if there is at least one match in the A,B tables. intersection of two tables

SQL statements are as follows:

select A.name,B.address from A

inner join B

on A.id = B.A_id

The query results are:

name address

from Beijing

Wang Shanghai

inner join is equivalent to sql:

SELECT A.name, B.address

FROM A, B

WHERE A.id = B.A_id

Note: An outer join returns all data in both data sets participating in the join, regardless of whether they have matching rows. Functionally, it is equivalent to

The two data sets are left and right outer joined, respectively, and then combined into a single result set using a union operation that eliminates duplicate rows

。(full join or full outer join )

SQL statements are as follows:

select * from A

full join B

The query results are:

id name id address A_id

1 Beijing 1

2 Li 1 Beijing 1

Beijing 1

1 2 Shanghai 3

2 Li 2 Shanghai 3

3 Wang 2 Shanghai 3

1 3 Nanjing 10

2 Li 3 Nanjing 10

3 Wang 3 Nanjing 10

Note: Return 3*3=9 records, i.e. Cartesian product

SQL statements are as follows:

SELECT * FROM A

CROSS JOIN B

The query results are:

id name id address A_id

1 Beijing 1

2 Li 1 Beijing 1

Beijing 1

1 2 Shanghai 3

2 Li 2 Shanghai 3

3 Wang 2 Shanghai 3

1 3 Nanjing 10

2 Li 3 Nanjing 10

3 Wang 3 Nanjing 10

CROSS JOIN is equivalent to:

select * from A,B

Note:

1. on A.id = B.id is equivalent to using(id)//the field names here should be the same

2. When MySQL retrieves information from a table, you can prompt it which index it selected.

This feature can be useful if EXPLAIN shows MySQL is using the wrong index in the list of possible indexes.

By specifying USE INDEX (key_list), you can tell MySQL to use the most appropriate of the possible indexes to find rows in the table.

The optional one-out syntax IGNORE INDEX (key_list) can be used to tell MySQL not to use a particular index.

Efficiency issues:

1. Inner join faster than left join

Note:Inner join is equivalent to sql: SELECT A.name, B.address FROM A, B WHERE A.id = B.A_id

So generally to use the general connection can be.

2. join field indexing

multiple off-sheet join

select A.*, B.f1,B.f2,B.fn,C.f1,C.f2,C.fn from A

left join B on A.id=B.id

left join C on C.id=A.id

where .......

About how to achieve external connection in mysql to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.