Join query

Join query is a matching query between two or more tables, commonly known as horizontal operation, that is, the final result will contain all the columns in these tables. MySQL has three join operations: cross join, inner join, outer join.

A CROSS JOIN, called a CROSS JOIN, performs a Cartesian product on two tables. It returns the composition of all the columns in the two tables. For example, if there are n columns in the left table and M columns in the right table, the result is N * M.

select * from orders as a  cross join orders as b;
+---------+--------+---------+--------+
| orderId | userId | orderId | userId |
+---------+--------+---------+--------+
|   10007 |      2 |   10001 |      1 |
|   10006 |      4 |   10001 |      1 |.|   10002 |      1 |   10007 |      2 |
|   10001 |      1 |   10007 |      2 |
+---------+--------+---------+--------+

49 rows in set (0.01 sec)

Copy the code

Since there are seven pieces of data in the Orders table, 49 pieces of data will be generated in the end, and another way to write this is as follows.

mysql> select * from orders as a  ,orders as b;
Copy the code

They all come up with the same results, but they’re written differently. This is written in 1989, when the American National Standards Institute standardized SQL, called THE ANSI SQL 89 standard, whereas the first one was written in 1992.

Cross joins are useful for quickly generating duplicate data, such as the following statement.

insert orders select a.orderId,a.userId from orders as a ,orders as b limit 10;
Copy the code

The following is the inner join, which is divided into two parts, producing the Cartesian product first, and then filtering it according to the filter condition of ON, which produces the value of the same record in both tables.

In addition to the equal operator (=) after ON, other operators such as greater than (>), less than (<), and not equal to (<>) can be used to form join conditions.

Select * from OUTER JOIN; select * from OUTER JOIN; select * from OUTER JOIN; select * from OUTER JOIN; select * from OUTER JOIN; MySQL supports LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT JOIN returns all the records in the LEFT table (Table1) and the matching records in the right table (Table2).

The RIGHT JOIN returns all the records in the RIGHT table (Table2) and the matching records in the left table (Table1).

Set operations

The UNION operator is used to combine two or more SELECT result sets and delete duplicate rows between SELECT statements. The following basic rules apply when using the UNION operator:

• allSELECTThe number and order of columns in the statement must be the same.
• Columns must also have the same data type.

For example, there is the following table

CREATE TABLE t1 (
    id INT PRIMARY KEY
);

CREATE TABLE t2 (
    id INT PRIMARY KEY
);

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (2), (3), (4);
Copy the code

Execute the following SQL

SELECT id FROM t1
UNION
SELECT id FROM t2;
Copy the code

The net result is this.

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)
Copy the code

The UNION statement removes duplicate data from the result row set by default, but you can use UNION ALL to retrieve duplicate records.

SELECT id FROM t1
UNION ALL 
SELECT id FROM t2;
Copy the code

The results are as follows

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  2 |
|  3 |
|  4 |
+----+
6 rows in set (0.00 sec)
Copy the code

The basic difference between a UNION and a JOIN is that a UNION combines result sets horizontally, while a JOIN statement combines result sets vertically.