JOIN

Kombinerar rader från två relationer baserat på kopplings villkor.

Syntax

relation { [ join_type ] JOIN relation [ join_criteria ] | NATURAL join_type JOIN relation }

Parametrar

  • relation

    Relationen som ska anslutas.

  • join_type

    Kopplings typ.

    Uttryck

    [ INNER ] | CROSS | LEFT [ OUTER ] | [ LEFT ] SEMI | RIGHT [ OUTER ] | FULL [ OUTER ] | [ LEFT ] ANTI

  • join_criteria

    Anger hur rader från en relation kombineras med raderna i en annan relation.

    Syntax:ON boolean_expression | USING ( column_name [ , ... ] )

    boolean_expression

    Ett uttryck med retur typen Boolean.

Kopplings typer

Inre koppling

Returnerar rader som har matchande värden i båda relationerna. Standard kopplingen.

Uttryck

relation [ INNER ] JOIN relation [ join_criteria ]

Vänster koppling

Returnerar alla värden från den vänstra relationen och de matchade värdena från den högra relationen, eller lägger till NULL om det inte finns någon matchning. Det kallas även för en vänster yttre koppling.

Uttryck

relation LEFT [ OUTER ] JOIN relation [ join_criteria ]

Höger koppling

Returnerar alla värden från den högra relationen och de matchade värdena från den vänstra relationen, eller lägger till NULL om det inte finns någon matchning. Den kallas även för en höger yttre koppling.

Uttryck

relation RIGHT [ OUTER ] JOIN relation [ join_criteria ]

Fullständig koppling

Returnerar alla värden från båda relationerna, lägger till NULL värden på sidan som inte har någon matchning. Det kallas även för en fullständig yttre koppling.

Uttryck

relation FULL [ OUTER ] JOIN relation [ join_criteria ]

Kors koppling

Returnerar kartesiska-produkten för två relationer.

Uttryck

relation CROSS JOIN relation [ join_criteria ]

Halv koppling

Returnerar värden från vänster sida av den relation som har en motsvarighet till höger. Det kallas även för en vänster halv koppling.

Uttryck

relation [ LEFT ] SEMI JOIN relation [ join_criteria ]

Anti-koppling

Returnerar värden från den vänstra relationen som inte har någon motsvarighet till höger. Det kallas även för ett vänster kopplings skydd.

Uttryck

relation [ LEFT ] ANTI JOIN relation [ join_criteria ]

Exempel

-- Use employee and department tables to demonstrate different type of joins.
SELECT * FROM employee;
+---+-----+------+
| id| name|deptno|
+---+-----+------+
|105|Chloe|     5|
|103| Paul|     3|
|101| John|     1|
|102| Lisa|     2|
|104| Evan|     4|
|106|  Amy|     6|
+---+-----+------+

SELECT * FROM department;
+------+-----------+
|deptno|   deptname|
+------+-----------+
|     3|Engineering|
|     2|      Sales|
|     1|  Marketing|
+------+-----------+

-- Use employee and department tables to demonstrate inner join.
SELECT id, name, employee.deptno, deptname
    FROM employee INNER JOIN department ON employee.deptno = department.deptno;
+---+-----+------+-----------|
| id| name|deptno|   deptname|
+---+-----+------+-----------|
|103| Paul|     3|Engineering|
|101| John|     1|  Marketing|
|102| Lisa|     2|      Sales|
+---+-----+------+-----------|

-- Use employee and department tables to demonstrate left join.
SELECT id, name, employee.deptno, deptname
    FROM employee LEFT JOIN department ON employee.deptno = department.deptno;
+---+-----+------+-----------|
| id| name|deptno|   deptname|
+---+-----+------+-----------|
|105|Chloe|     5|       NULL|
|103| Paul|     3|Engineering|
|101| John|     1|  Marketing|
|102| Lisa|     2|      Sales|
|104| Evan|     4|       NULL|
|106|  Amy|     6|       NULL|
+---+-----+------+-----------|

-- Use employee and department tables to demonstrate right join.
SELECT id, name, employee.deptno, deptname
    FROM employee RIGHT JOIN department ON employee.deptno = department.deptno;
+---+-----+------+-----------|
| id| name|deptno|   deptname|
+---+-----+------+-----------|
|103| Paul|     3|Engineering|
|101| John|     1|  Marketing|
|102| Lisa|     2|      Sales|
+---+-----+------+-----------|

-- Use employee and department tables to demonstrate full join.
SELECT id, name, employee.deptno, deptname
    FROM employee FULL JOIN department ON employee.deptno = department.deptno;
+---+-----+------+-----------|
| id| name|deptno|   deptname|
+---+-----+------+-----------|
|101| John|     1|  Marketing|
|106|  Amy|     6|       NULL|
|103| Paul|     3|Engineering|
|105|Chloe|     5|       NULL|
|104| Evan|     4|       NULL|
|102| Lisa|     2|      Sales|
+---+-----+------+-----------|

-- Use employee and department tables to demonstrate cross join.
SELECT id, name, employee.deptno, deptname FROM employee CROSS JOIN department;
+---+-----+------+-----------|
| id| name|deptno|   deptname|
+---+-----+------+-----------|
|105|Chloe|     5|Engineering|
|105|Chloe|     5|  Marketing|
|105|Chloe|     5|      Sales|
|103| Paul|     3|Engineering|
|103| Paul|     3|  Marketing|
|103| Paul|     3|      Sales|
|101| John|     1|Engineering|
|101| John|     1|  Marketing|
|101| John|     1|      Sales|
|102| Lisa|     2|Engineering|
|102| Lisa|     2|  Marketing|
|102| Lisa|     2|      Sales|
|104| Evan|     4|Engineering|
|104| Evan|     4|  Marketing|
|104| Evan|     4|      Sales|
|106|  Amy|     4|Engineering|
|106|  Amy|     4|  Marketing|
|106|  Amy|     4|      Sales|
+---+-----+------+-----------|

-- Use employee and department tables to demonstrate semi join.
SELECT * FROM employee SEMI JOIN department ON employee.deptno = department.deptno;
+---+-----+------+
| id| name|deptno|
+---+-----+------+
|103| Paul|     3|
|101| John|     1|
|102| Lisa|     2|
+---+-----+------+

-- Use employee and department tables to demonstrate anti join.
SELECT * FROM employee ANTI JOIN department ON employee.deptno = department.deptno;
+---+-----+------+
| id| name|deptno|
+---+-----+------+
|105|Chloe|     5|
|104| Evan|     4|
|106|  Amy|     6|
+---+-----+------+