Can someone explain table joins?

[Philip Toews]

Does anyone know of a simple tutorial that explains how table joins work? I want to understand what actually happens in MySQL when you do a table join. In particular I want to know more about LEFT JOINs. It will make it a lot easier to understand how to use joins if I know what happens when you do one!

Devshed has an article www.devshed.com/Server_Side/MySQL/Join/page1.html , but it is kind of confusing. Kevin's book makes a good start on it, but I'd like an article that describes it a little more.

Anyone?

[Aes]

I do not have an article at hand that explains it particularly well, but I can give a definition. Basically, it's a way of comparing data across multiple tables; I don't know that there's a simpler way to explain it.

LEFT joins are the same, comparing cross-table data, yet only pulling out the x number of defined character starting from the left in a MySQL data field.

Another note, joins are really a good way to accomplish subqueries without the ... well ... subqueries.

They're much more inefficient (MySQL will soon support subqueries, hopefully), but with something as light and fast as MySQL that does not produce a very noticeable performance hit.

[DR_LaRRY_PEpPeR]

a LEFT JOIN is the same as a regular join with the exception that it forces a row to appear in the table that's being LEFT JOINed, even if a row doesn't match the join criteria. if there's no matching row, then its row will contain NULL values.

Originally posted by Aes
They're much more inefficient (MySQL will soon support subqueries, hopefully), but with something as light and fast as MySQL that does not produce a very noticeable performance hit.

are you sure about that? i mean, i don't know at all, but it almost seems that a subquery would be a little slower since another query has to be run. but i don't know.

and i think joins are still used even in DBs that do support subqueries.

[andnaess]

I think he meant that subqueries are slower than joins, which is true.

[freakysid]

Phillip Greenspun's SQL for Web Nerds may be of interest. There is a chapter devoted to joins. Note that later on, the ebook becomes more focused on Oracle specifics such as stored procedures, but it might be what you are looking for,

[andnaess]

A natural (inner) join is defined in relational algebra simply as a joining of tuples from one or more relations on one or more attributes. A simple example is (U INNER JOIN V):

Relation U:
A  B
====
1  2
2  2
3  3

Relation V:
B  C
====
2  3
3  5
4  5

To take the natural join, note which tuples agree on the B attribute, and join these. From the above you get the relation:

A  B  C
=======
1  2  3
2  2  3
3  3  5

Now, as you can see, one tuple was left out from V (4,5). This is called a dangling tuple. The difference between an inner and an outer join is that in an outer join, dangling tuples are not removed from the resulting relation, thus an outer join using the above sample data would produce:

A  B  C
=======
1  2  3
2  2  3
3  3  5
-  4  5

Where '-' is the NULL value. There are two much used versions of outer joins -- the left and the right outer join. In the left outer join, only tuples from the left relation is kept. In the right outer join, only tuples from the right relation are kept. In the above example, a left outer join would result in the same relation as the natural join, whereas a right outer join would produce the same as the natural outer join.

[Philip Toews]

Thanks for replying guys. It leads to another simple question:


When do you use each particular kind of join? When looking at the following examples can you gurus tell me if I got it right?

a) CROSS JOINS bring two or more tables together. IT matches each row from each table to each row in the other tables. The result set is therefore REALLY big!

SELECT * FROM authors, books;

b) EQUI-JOINS brings together data from multiple tables based on corresponding data between the two. In the following example both tables share a column that has the authors ID number...it makes it easy to then create a result that has one row for each record where the conditions match up. In this case, one row with the author's name and the title of the book for each book that author has written.

SELECT authors.name, books.title from authors, books WHERE authors.ID=books.authorID;

c) NON EQUI-JOINS are joins that bring toether data from two tables where certain conditions within each table are dissimilar. In this case, you get a row for every book for every author with an ID number greater than 2.

SELECT authors.name, books.title FROM authors, books, WHERE books.authorID > 2;


OK...big breath now....

c) LEFT JOINS bring together tables where there may be null entries in the second table being joined. A LEFT JOIN forces a row to show up in the first (LEFT) table even if there is no matching result in the second table(s).

SELECT authors.name, books.title LEFT JOIN ON author.ID=books.authorID.

In the example above a row would be created for every author in the authors table for every book they had written. If there were no entries at all for books they had written there will still be one entry with the author's name in one column and NULL in the other.

LEFT JOINS are useful for situations where you need to create result rows even if those rows contain NULL values.

Did I get this right? To help clarify things could you folks tell me WHEN we should use each kind of join...ie: practical examples?

Thanks.

p

[Philip Toews]

Thanks andnaess,

I'll try to decipher your reply....I think I'll drink some coffee first!

I definately should have tried hard in Algebra class!

p

[Aes]

Subqueries are much more efficient than joins in Oracle; however considering MySQL's inability to use subqueries as of the latest release, perhaps this doesn't hold true for MySQL.