Please explain MySQL Joins in simple language

Please ex开发者_JS百科plain 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.

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.

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.

I'm interpreting your question to mean joins in a very general sense, not each type of join, so if this is off the mark then I apologize:

Basically, joins enable you to get data from multiple tables in a single query by adding columns to your result set. So if you had the following tables:

Books   (ID, Title, AuthorID)
Authors (ID, Name)

You could get a result set that looked like this:

  Book                    |   Author
'The Sirens of Titan'     | 'Kurt Vonnegut'
'The Old Man and the Sea' | 'Earnest Hemingway'

By joining the two tables together like so:

select Books.Title as Book, Authors.Name as Author
from Books
inner join Authors on Authors.ID = Books.AuthorID

An inner join is the simplest type of join; it may be difficult to understand the point of outer joins without first having a strong grasp of inner joins and their uses.