JOIN-QUERIES-IN-MYSQL

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

1. SELECT
2. INNER JOIN
3. LEFT OUTER JOIN OR LEFT JOIN
4. RIGHT OUTER JOIN OR RIGHT JOIN
5. SEMI JOIN
6. ANTI SEMI JOIN
7. LEFT OUTER JOIN with exclusion
8. RIGHT OUTER JOIN with exclusion
9. FULL OUTER JOIN
10. FULL OUTER JOIN with exclusion
11. TWO INNER JOINS
12. TWO LEFT OUTER JOINS
13. INNER JOIN and a LEFT OUTER JOIN

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;