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;

MySql Tutorial

MySql Video Tutorial

Learn All in Tamil © Designed & Developed By Tutor Joes | Privacy Policy | Terms & Conditions