MySQL Full Join implementation, mysqljoin

MySQL Full Join implementation, mysqljoin

MySQL Full Join is implemented because MySQL does not support full join. The following is an alternative method: left join + union (deduplication is allowed) + right joinselect * from A left join B on. id = B. id (where condition) unionselect *From A right join B on A. id = B. id (where condition ); 

Mysql implements full join

Full JOIN is supported for Oracle, DB2, SQL Server, and PostgreSQL.
However, MySQL does not.
You can use
Left join + UNION + right join.

Below are the test tables and test data
-- Student table create table student (sno int, sname varchar (10), icno int); -- canteen IC card table create table ic (icno int, icname varchar (10), ICMoney INT ); insert into student VALUES (1, 'zhang san', 1); insert into student VALUES (2, 'Li si', 2); insert into student VALUES (3, 'wang wu ', NULL); insert into ic values (1, 'zhang san', 500); insert into ic values (2, 'Li si', 250); insert into ic values (3, 'Mr Zhao ', 600 );

The following is the result of SQL Server using full join.
SELECT student. SNO, student. SNAME, IC. ICNAME, IC. ICMoneyFROM student full join ic on (student. ICNO = IC. ICNO); goSNO sname icname ICMoney ----------- ---------- --------------- 1 Zhang San 500 2 Li Si 250 3 Wang Wu NULL Zhao instructor 600 (4 rows affected)

The following output is not supported by MySQL:
Mysql> SELECT-> student. SNO,-> student. SNAME,-> IC. ICNAME,-> IC. ICMoney-> FROM-> student-> full join ic-> ON (student. ICNO = IC. ICNO); ERROR 1054 (42S22): Unknown column 'student. SNO 'in 'field list'

The following is the MySQL left join + UNION + right join method.
Mysql> SELECT-> student. SNO,-> student. SNAME,-> IC. ICNAME,-> IC. ICMoney-> FRO... the remaining full text>

Mysql full join

A Left join B union B Left JOIN
That is
SELECT... from a left join B ON (JOIN condition)
UNION
SELECT... from B LEFT JOIN A ON (JOIN condition)

Note:
The principle here is that the UNION operation will merge duplicates.
Previous
A left join B, which includes both A and B and does not have B with.
The
B left join a, which includes both A and B, but not B.

After UNION processing, the final result is include
A and B both exist, and A does not have B. It does not have A with B.

However, if the query itself contains duplicate records when you perform a left join B query, this UNION method may not be used.