Join Queries in MySQL


A JOIN queries is used to combine rows from two or more tables, based on a related column between them.

  • SELECT
  • INNER JOIN
  • LEFT OUTER JOIN OR LEFT JOIN
  • RIGHT OUTER JOIN OR RIGHT JOIN
  • SEMI JOIN
  • ANTI SEMI JOIN
  • LEFT OUTER JOIN with exclusion
  • RIGHT OUTER JOIN with exclusion
  • FULL OUTER JOIN
  • FULL OUTER JOIN with exclusion
  • TWO INNER JOINS
  • TWO LEFT OUTER JOINS
  • INNER JOIN and a LEFT OUTER JOIN

SELECT


MySQL SELECT

Example:


SELECT
  *
FROM
  Table1;
SELECT
  *
FROM
  Table2;

INNER JOIN


MySQL INNER JOIN

Example:


SELECT
  *
FROM
  Table1 t1
  INNER JOIN Table2 t2 ON t1.fk = t2.id;

LEFT OUTER JOIN OR LEFT JOIN


MySQL LEFT OUTER JOIN OR LEFT JOIN

Example:


SELECT
  *
FROM
  Table1 t1
  LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id;

RIGHT OUTER JOIN OR RIGHT JOIN


MySQL RIGHT OUTER JOIN OR RIGHT JOIN

Example:


SELECT
  *
FROM
  Table1 t1
  RIGHT OUTER JOIN Table2 t2 ON t1.fk = t2.id;

SEMI JOIN


MySQL SEMI JOIN

Example:


SELECT
  *
FROM
  Table1 t1
WHERE
  EXISTS (
    SELECT
      1
    FROM
      Table2 t2
    WHERE
      t1.fk = t2.id
  );
 

ANTI SEMI JOIN


MySQL ANTI SEMI JOIN

Example:


SELECT
  *
FROM
  Table1 t1
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      Table2 t2
    WHERE
      t1.fk = t2.id
  );
 

LEFT OUTER JOIN with exclusion


MySQL LEFT OUTER JOIN with exclusion

Example:


SELECT
  *
FROM
  Table1 t1
  LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id
WHERE
  t2.id is null;

RIGHT OUTER JOIN with exclusion


MySQL RIGHT OUTER JOIN with exclusion

Example:


SELECT
  *
FROM
  Table1 t1
  RIGHT OUTER JOIN Table2 t2 ON t1.fk = t2.id
WHERE
  t1.fk is null;
 

FULL OUTER JOIN


MySQL FULL OUTER JOIN

Example:


SELECT
  *
FROM
  Table1 t1
  LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id
UNION
SELECT
  *
FROM
  Table1 t1
  RIGHT OUTER JOIN Table2 t2 ON t1.fk = t2.id;
 

FULL OUTER JOIN with exclusion


MySQL  FULL OUTER JOIN with exclusion

Example:


SELECT
  *
FROM
  Table1 t1
  LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id
WHERE
  t2.id IS NOT NULL
UNION
SELECT
  *
FROM
  Table1 t1
  RIGHT OUTER JOIN Table2 t2 ON t1.fk = t2.id
WHERE
  t1.ID IS NOT NULL;
 

TWO INNER JOINS


MySQL TWO INNER JOINS

Example:


SELECT
  *
FROM
  Table1 t1
  INNER JOIN Table2 t2 ON t1.fk = t2.id
  INNER JOIN Table3 t3 ON t1.fk_table3 = t3.id;
 

TWO LEFT OUTER JOINS


MySQL TWO LEFT OUTER JOINS

Example:


SELECT
  *
FROM
  Table1 t1
  LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id
  LEFT OUTER JOIN Table3 t3 ON t1.fk_table3 = t3.id;

INNER JOIN and a LEFT OUTER JOIN


MySQL INNER JOIN and a LEFT OUTER JOIN

Example:


SELECT
  *
FROM
  Table1 t1
  INNER JOIN Table2 t2 ON t1.fk = t2.id
  LEFT OUTER JOIN Table3 t3 ON t1.fk_table3 = t3.id;