Table of Contents
1】INNER JOIN ON inner join (only The default is inner join when writing join)
2】Outer join
2.1. Left outer join: display all records of the left table
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
Copy after login
" >
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
Copy after login
Home Database Mysql Tutorial How to implement full external connection in Mysql

How to implement full external connection in Mysql

May 31, 2023 am 08:37 AM
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
    Copy after login

    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;
    Copy after login

    How to implement full external connection in Mysql

    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;
    Copy after login

    How to implement full external connection in Mysql

    ##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;
    Copy after login

    How to implement full external connection in Mysql

    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;
    Copy after login

    How to implement full external connection in Mysql

    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!

    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

    Hot Article Tags

    Notepad++7.3.1

    Notepad++7.3.1

    Easy-to-use and free code editor

    SublimeText3 Chinese version

    SublimeText3 Chinese version

    Chinese version, very easy to use

    Zend Studio 13.0.1

    Zend Studio 13.0.1

    Powerful PHP integrated development environment

    Dreamweaver CS6

    Dreamweaver CS6

    Visual web development tools

    SublimeText3 Mac version

    SublimeText3 Mac version

    God-level code editing software (SublimeText3)

    PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

    PHP's big data structure processing skills

    How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

    How to optimize MySQL query performance in PHP?

    How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

    How to use MySQL backup and restore in PHP?

    What are the application scenarios of Java enumeration types in databases? What are the application scenarios of Java enumeration types in databases? May 05, 2024 am 09:06 AM

    What are the application scenarios of Java enumeration types in databases?

    How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

    How to insert data into a MySQL table using PHP?

    How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

    How to fix mysql_native_password not loaded errors on MySQL 8.4

    How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

    How to use MySQL stored procedures in PHP?

    Performance optimization strategies for PHP array paging Performance optimization strategies for PHP array paging May 02, 2024 am 09:27 AM

    Performance optimization strategies for PHP array paging

    See all articles