How to implement full external connection in Mysql

MySql multi-table query only provides inner joins, left outer joins and right outer joins:

table_reference {[INNER] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr

1】INNER JOIN ON inner join (only The default is inner join when writing join)

SELECT * FROM emp e JOIN dept d ON e.deptno=d.deptno;

2】Outer join

Both left outer join and right outer join will use one table as the base table, all the contents of the table will be displayed, and then the matching contents of the two tables will be added.

If the data in the base table is not recorded in another table.

Then the column in the associated result set row displays a null value (NULL).

2.1. Left outer join: display all records of the left table

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

##2.2. Right outer join: display all records of the right table

SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

2.3. Full outer join

There are no restrictions on the left table and the right table, all records are displayed, and the insufficient places in the two tables are filled with null;

That is:

• Left outer join = all records in the left table are related results

• Right outer join = all records in the right table Related results

• Full outer join=All records in the left table All records in the right tableRelated results=Left outer joinRight outer join-Related results (ie, deduplication)

So who can do it in MYSQL?

UNION

The UNION operator is used to combine the result sets of two or more SELECT statements.

Note: By default, the UNION operator selects different values. If duplicate values ​​are allowed, use UNION ALL.

Therefore, you can use full outer join:

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

Note: The above connections are all equivalent joins; in Oracle, you can use full join for full outer join; Please don't get confused.

The above is the detailed content of How to implement full external connection in Mysql. For more information, please follow other related articles on the PHP Chinese website!