Shulou(Shulou.com)06/01 Report--

For the development of applications using the database, it is inevitable to use federated queries, SQL commonly used federated queries inner join, outer join and cross join; these three differences many people should not be very clear, including myself, let's take a look, if you use join with tables, defects in the case is inner join, in addition, the development of the use of left join and right join belong to outer join, and outer join also includes full join. Next, I use patterns to let you know the difference between them.

There are two tables available. Table An is the one on the left. Table B is the table on the right. Each has four records, two of which have the same name:

The result of 1.INNER JOIN is the intersection of AB.

SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name

2.LEFT [OUTER] JOIN produces a complete set of table A, while those in table B have values, and those that do not match are replaced by null values.

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name

3.RIGHT [OUTER] JOIN produces a complete set of table B, while table A matches have values, and those that do not match are replaced by null values.

SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name

The figure is similar to left join.

4.FULL [OUTER] JOIN produces the union of An and B. For records that do not match, null is used as the value.

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

You can use is NULL to find out the values that do not match:

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.nameWHERE TableA.id IS null OR TableB.id IS null

5. CROSS JOIN combines the data of Table An and Table B into a Numeric combination, that is, Cartesian product. For example, this example will produce 4 records, 4 records and 16 records. We must filter the data during the development process, so this is rarely used.

SELECT * FROM TableA CROSS JOIN TableB

Through the comparison above, I believe the difference between inner join, outer join and cross join is clear at a glance.

Supplement

Why is it best to use join? Because if you join the table with ",", the A _ Magi B table directly produces the Cartesian product, and then uses where to filter the results. But when join joins, table B will filter according to the conditions behind on, and then filter the results according to the where conditions.

CROSS JOIN is a Cartesian product according to the sql standard. But for mysql, CROSS JOIN is the equivalent of INNER JOIN.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.