How to use full join...on... in mysql

Usage of full join...on...in mysql

Most developers may be confused, why do I write my sql statement? All wrong.

It’s very simple because full join … on … does not support mysql database, only Oracle database.

Without further ado, let’s just give an example.

select a.* FROM
    (select
    table1.*,table2.*
    from table1 left JOIN table2 on table1.id=table2 .tid <-- 用左外连接 -->
    UNION <-- 通过UNION来链接 -->
    select
    table1.*,table2.*
    from table1 right JOIN table2 on table1.id=table2 .tid) as a <-- 用右外连接 -->
    where 
    a.id = #{id} <-- 如果要根据条件查询，必须要把上面括号里的当做子查询，条件必须写在外面 -->
    ORDER BY id <-- 其它操作跟在后面 -->

Doesn’t it feel very simple, but for novices, this may be a struggle for a long time, so it’s over here.

What are the types of join connections in mysql?

What are the types of joins?

(1) Inner join

select * from user1 a INNER JOIN user2 b on a.id=b.id

INNER JOIN inner join shows the intersection between two tables , that is, the information that meets the query conditions (a.id=b.id in the above SQL statement) is obtained.

(2) JOIN connection left join/left outer join (LEFT JOIN/LEFT OUTER JOIN)

select * from user1 a LEFT JOIN user2 b on a.id=b.id

The left join queries all the information of the left table and There are two parts: the information that meets the query conditions (for example, a.id=b.id above, that is, the information in the left and right tables that meets the associated query conditions).

(3) Right JOIN/RIGHT OUTER JOIN of JOIN connection(RIGHT JOIN/RIGHT OUTER JOIN)

select * from user1 a RIGHTJOIN user2 b on a.id=b.id

The right join queries the right table on the right side of RIGHT JOIN All the information plus the left and right sides that meet the associated query conditions (that is, the a.id=b.id condition above).

(4) Full join …on…)

select * from user1 a FULL JOIN user2 b on a.id=b.id

Full outer join is actually a collection of left join and right join, which means that it will query all the data of the left table and the right table.

(5) Cross join(cross join...)

select * from user1 a CROSS JOIN user2 b on a.id=b.id

Cross join, also known as Cartesian product, the number of rows returned by the query is equal to two table rows product of numbers.

The above is the detailed content of How to use full join...on... in mysql. For more information, please follow other related articles on the PHP Chinese website!