[go: up one dir, main page]
Scribd
Upload
236 views1 page

VennDiagram2 PDF

This document describes different types of SQL JOINs: 1. INNER JOIN returns rows that have matching values in both tables. LEFT OUTER JOIN and RIGHT OUTER JOIN return all rows from the left/right table, and the matched rows from the right/left table. 2. SEMI JOIN and ANTI SEMI JOIN are similar to INNER JOIN but return fewer duplicate rows. 3. LEFT OUTER JOIN and RIGHT OUTER JOIN can be combined with exclusion filters to return unmatched rows. FULL OUTER JOIN combines the results of a LEFT and RIGHT JOIN. 4. Multiple tables can be joined together using multiple JOIN types like INNER JOIN and LEFT OUTER JOIN.

Uploaded by

Cesar D. Puentes F.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
236 views1 page

VennDiagram2 PDF

This document describes different types of SQL JOINs: 1. INNER JOIN returns rows that have matching values in both tables. LEFT OUTER JOIN and RIGHT OUTER JOIN return all rows from the left/right table, and the matched rows from the right/left table. 2. SEMI JOIN and ANTI SEMI JOIN are similar to INNER JOIN but return fewer duplicate rows. 3. LEFT OUTER JOIN and RIGHT OUTER JOIN can be combined with exclusion filters to return unmatched rows. FULL OUTER JOIN combines the results of a LEFT and RIGHT JOIN. 4. Multiple tables can be joined together using multiple JOIN types like INNER JOIN and LEFT OUTER JOIN.

Uploaded by

Cesar D. Puentes F.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

MySQL JOIN Types

Created by Steve Stedman


SELECT * SELECT *
FROM Table1; FROM Table1 t1
Table 1 Table 2 Table 1 Table 2
INNER JOIN Table2 t2
SELECT * ON t1.fk = t2.id;
FROM Table2; INNER JOIN
SELECT from two tables

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

SELECT * SELECT *
FROM Table1 t1 FROM Table1 t1
WHERE EXISTS (SELECT 1 Table 1 Table 2 WHERE NOT EXISTS (SELECT 1
Table 1 Table 2
FROM Table2 t2 FROM Table2 t2
WHERE t1.fk = t2.id WHERE t1.fk = t2.id
); );
SEMI JOIN – Similar to INNER JOIN, with less duplication. ANTI SEMI JOIN

SELECT * SELECT *
FROM Table1 t1 FROM Table1 t1
Table 1 Table 2 LEFT OUTER JOIN Table2 t2 Table 1 Table 2 RIGHT OUTER JOIN Table2 t2
ON t1.fk = t2.id ON t1.fk = t2.id
WHERE t2.id is null; WHERE t1.fk is null;
LEFT OUTER JOIN with exclusion RIGHT OUTER JOIN with exclusion

SELECT * FROM Table1 t1 SELECT * FROM Table1 t1


LEFT OUTER JOIN Table2 t2
LEFT OUTER JOIN Table2 t2
ON t1.fk = t2.id
ON t1.fk = t2.id Table 1 Table 2 WHERE t2.id IS NOT NULL
Table 1 Table 2
UNION UNION
SELECT * FROM Table1 t1 SELECT * FROM Table1 t1
RIGHT OUTER JOIN Table2 t2
RIGHT OUTER JOIN Table2 t2 FULL OUTER JOIN with
FULL OUTER JOIN ON t1.fk = t2.id;
ON t1.fk = t2.id
exclusion WHERE t1.ID IS NOT NULL;

SELECT * SELECT *
Table 3 Table 3
FROM Table1 t1 FROM Table1 t1
INNER JOIN Table2 t2 LEFT OUTER JOIN Table2 t2
Table 1 Table 2 ON t1.fk = t2.id Table 1 Table 2 ON t1.fk = t2.id
INNER JOIN Table3 t3 LEFT OUTER JOIN Table3 t3
ON t1.fk_table3 = t3.id; ON t1.fk_table3 = t3.id;
Two INNER JOINs Two LEFT OUTER JOINS

SELECT *
Table 3
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.fk = t2.id
Table 1 Table 2
LEFT OUTER JOIN Table3 t3
ON t1.fk_table3 = t3.id;
INNER JOIN and a LEFT OUTER JOIN

Created By Steve Stedman http://SteveStedman.com Twitter @SqlEmt

You might also like