Please explain MySQL Joins in simple language

Question

Please explain to me joins in simple language. Please do not post a web link as I need to read how a developer understand it, not an author.

Answer 1

Best I can point you to is A Visual Explanation of SQL Joins.

The diagrams helped me a lot.

---

Adding the main diagrams from the linked post here.

Inner Join

Inner join produces only the set of records that match in both Table A and Table B.

Full outer join

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

Left outer join

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

Answer 2

Given Table Person And Information

SELECT *
FROM Person INNER JOIN
Information ON Person.ID = Information.ID

Will only return rows from both tables where both tables have the same IDs. So only if the ID exists in both Person and Information will the row be returned.

SELECT *
FROM Person LEFT JOIN
Information ON Person.ID = Information.ID

Will return all rows from Person, and only those that match from Information, where it does not match, it will return NULLs

SELECT *
FROM Person LEFT JOIN
Information ON Person.ID = Information.ID
WHERE Information.ID IS NULL

Will return all rows from Person that DOES NOT HAVE an entry in Information. This shows you the list of persons that do not have their Informaton updated yet.